I get an error due to one of the current descriptions.. Also like 79 which has C4 shows 4 but no CNT so its value should not show up.

These two rows

,('TWI-50','RSV BEAUTIFULORA C4')
,('TWI-51','5 - LOXIN (BOSWELLIA EXT)')

USE tempdb
--===== Create the test table with
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF OBJECT_ID('[dbo].[PART]','U') IS NOT NULL
DROP TABLE [dbo].[PART];
GO

CREATE TABLE [dbo].[PART](
  [ROWID] [int] IDENTITY(1,1) NOT NULL,
  [ID] [nvarchar](30) NOT NULL,
  [DESCRIPTION] [nvarchar](120) NULL
CONSTRAINT [PK_PART] PRIMARY KEY CLUSTERED
(
  [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

--===== Insert the test data into the test table
INSERT INTO [dbo].[PART](ID, DESCRIPTION)
VALUES
('AMB-02','LOVIDIA 30 CNT/BT, REGULAR')
,('AMB-03','GLP1 SUPPORT 60 CNT/BT')
,('DEV-02','DEVROM CHEWABLE 100 CNT BT')
,('DOT-01','ZENDOCRINE 60 CNT/BT, CAPS')
,('DOT-04','US DIGESTZEN, US 100 CNT/BT, TB')
,('LIF-06','PROTANDIM NRF2 (US) 30 CNT. BOTTLE')
,('LIF-07','PETANDIM FOR DOGS, 30 CNT BT (US)')
,('LIF-09','PROTANDIM NRF2 30 CNT (JP)')
,('LIF-10','PROTANDIM HK 30 CNT. BOTTLE (HK CHI)')
,('LIF-12','PROTANDIM CA 30 CNT. BTL (CA ENG/FRE)')
,('LIF-13','PETANDIM FOR DOGS, 30 CNT BT (JP)')
,('LIF-22','PROTANDIM NRF1 JP 60 CNT. BT')
,('LIF-23','PROTANDIM NRF2 TH 30 CNT. BT')
,('LIF-25','PHYSIQ FAT BURN JP 120 CNT. BT')
,('LIF-26','PROTANDIM NRF1 TH 60 CNT. BT')
,('LIF-27','PROTANDIM NRF1 EU 60 CNT. BT')
,('LIF-29','PROTANDIM NRF2 MX 30 CNT. BT')
,('LIF-30','PROTANDIM NRF1 CA 60 CNT BT ')
,('LIF-31','PROTANDIM NRF1 MX 60 CNT. BT')
,('LIF-32','PROTANDIM NRF1 60 CNT US ENG, BT ')
,('LIF-33','PROTANDIM NRF1 60 CNT HK ENG, BT ')
,('LIF-34','PROTANDIM NRF2 TW 30 CNT. BT')
,('NUS-08','BOTANAGAR 120 CNT/BT (TAIWAN)')
,('NUS102','VITOX 180 CNT/BT (JAPAN), CAPSULE')
,('NUS120','TeGREEN 30 CNT/ BT (JAPAN), CAPS(KENKO)')
,('NUS141','METABOTRIM DIET (JP) 60 CNT/BT , CAPS')
,('NUS145','SEASONAL CARE 90 CNT/BT (JP), CAPS')
,('NUS169','DETOX FORMULA 60 CNT/BT (US), CAPSULE')
,('NUS170','TEGREEN 120 CNT/BT (SG)')
,('NUS172','BIO GINKGO 60 CNT/BT (MY)')
,('NUS184','DIGESTIVE FORMULA 30 CNT (TW) BT')
,('NUS217','FLEX FORMULA 120 CNT/BT (SG)')
,('NUS218','TEGREEN 30 CNT/BT (INDONESIA)')
,('NUS221','PROBIO PCC (KO) 30 CNT/BT')
,('NUS222','PROBIO PCC (TW) 30 CNT/BT')
,('NUS224','PROBIO PCC (JP) 30 CNT/BT')
,('NUS225','PROBIO PCC (SG) 30 CNT/BT ')
,('NUS227','REISHIMAX 60 CNT/BT (MY) CAPSULE')
,('NUS229','BIO GINKGO REFORMULATED 60 CNT/BT')
,('NUS238','PROBIO PCC (HK) 30 CNT/BT ')
,('NUS246','REISHI EX 60 CNT/BT (JP), CAPSULE')
,('NUS259','BIO GINKGO 27/7 60 CNT/BT (HK)')
,('NUS260','BONE FORMULA (US) 180 CNT/BT')
,('NUS265','UNFG COMPLEX F (EU) 120 CNT BTL')
,('NUS266','ESTERA PHASE II TRANSITION (US) 30CNT/BT')
,('NUS267','ESTERA PHASE III MAINTENANCE (US) 60 CNT')
,('NUS275','TeGREEN 30 CNT/ BT(MX), CAPSULES')
,('NUS276','GENLOC TR90 FIT JAPAN 90 CNT BOTTLE ')
,('NUS279','PROBIO 30 CNT/ BT (US), CAPSULES')
,('NUS280','CORTITROL 60 CNT/ BT (US), CAPSULES')
,('NUS290','UNFG REISHIMAX 60 CNT/BT (EU), CAPS')
,('NUS292','DIGESTIVE FORMULA (US) 60 CNT/BT')
,('NUS293','ESTERA CRANBERRY 60 CNT/BT (US)')
,('NUS303','UNFG BONE FORMULA (EU) 180 CNT/BT')
,('NUS313','PROBIO UNFG 30 CNT/ BT (EU), CAPSULES')
,('NUS318','CORTITROL (HK 2017) 60 CNT/BT ')
,('NUS319','UNFG JUNGAMALS 90 CNT/BT (HK)')
,('NUS-41','LIFEPAK TEEN 120 CNT/BT, US')
,('PYC-08','PBGS+ 60 CNT/BT')
,('PYC-09','PBGS+ 120 CNT/BT')
,('PYC-11','PBGS+ 120 CNT/BT (KOREA)')
,('PYC-12','PBGS+ 60 CNT/BT (MEXICO)')
,('SHA-28','RITESTART KIDS & TEENS 120 CNT/BT')
,('SHK-11','HERB-LAX 240 CNT TAB US (KOSHER)')
,('SHK-12','HERB-LAX 60 CNT TAB US (KOSHER)')
,('SHK-14','HERB-LAX 240 CNT TAB CAN')
,('SHK-21','PHYTOCOL-ST TB,120 CNT BT, MY ')
,('SIM-01','BABY GUM RELIEF TB 135CNT')
,('TWI-20','RESVITAL RESVERATROL 500MG 60CNT')
,('TWI-21','RESERVEAGE KERATIN BOOSTER W BIOTIN 60CNT')
,('TWI-24','RESERVEAGE TRES BEAUTY 3 90CNT')
,('TWI-25','RESVITAL BEAUTY 3 90CNT')
,('TWI-27','RESERVEAGE BEAUTIFUL LEGS W/DIOSMIN 30CNT')
,('TWI-29','RESERVEAGE PROSTATE SCIENCE 60CNT')
,('TWI-30','RESERVEAGE KERATIN BOOSTER W/BIOTIN FOR MEN 60CNT')
,('TWI-47','RSV BEAUTIFULORA A')
,('TWI-48','RSV BEAUTIFULORA B')
,('TWI-49','RSV BEAUTIFULORA C')
,('TWI-50','RSV BEAUTIFULORA C4')
,('TWI-51','5 - LOXIN (BOSWELLIA EXT)')
,('YOU-07','ESSENTIALZYME 90CNT BI-LAYER TAB. BOTTLE');
GO

SELECT
[p].*
,[Qty] = RIGHT(LEFT(RTRIM([p].[DESCRIPTION]),LEN(RTRIM([p].[DESCRIPTION])) - (PATINDEX('%[0-9]%',LTRIM(REVERSE([p].[DESCRIPTION]))) - 1))
      ,(PATINDEX('%[^0-9]%',REVERSE(LEFT(RTRIM([p].[DESCRIPTION]), LEN(RTRIM([p].[DESCRIPTION])) - (PATINDEX('%[0-9]%',LTRIM(REVERSE([p].[DESCRIPTION]))) - 1)))) - 1))
FROM
[dbo].[PART] AS [p]
GO