SQL Server 2016

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Declare @YourTable Table ([Type] int,[Description] varchar(150))
Insert Into @YourTable Values 
 (2,'1LAPTOP APPLE 15.5" MODEL MACKBOOK PRO,S/N W80461WCAGX, WITH CHARGER')
,(1,'1RING 2.3PW 14K')
,(2,'DRILL RIOBY, MODEL D5521 S/N77720')
,(2,'TRIMMER TORO, MODEL 0242 S/N 66759')
,(2,'CELL SAMSUNG NOTE3, MODEL SM-N900T S/N RV8F90YLZ9W')

Select A.Type
      ,A.Description
      ,C.*
 From  @YourTable A
 Cross Apply (values ( replace(
                       replace(
		       replace(
		       replace(A.Description,',',' ')
		       ,'  ',' ')
		       ,'Model ','Model')
		       ,'S/N ','S/N')
                     ) 
	      )B(CleanString)
 Cross Apply (
		Select Quantity  = IsNull(left(max(case when RetSeq=1 then RetVal end),NullIf(patindex('%[^0-9]%',max(case when RetSeq=1 then RetVal end)) -1,0)),1)
		      ,Name      = substring(max(case when RetSeq=1 then RetVal end),patindex('%[^0-9]%',max(case when RetSeq=1 then RetVal end)),charindex(' ',max(case when RetSeq=1 then RetVal end)+' ')-1)
		      ,Weight    = IIF(A.Type=2,null,try_convert(decimal(5,1),replace(max(case when RetVal like '%PW' then RetVal end),'PW','')))
		      ,Purity    = try_convert(smallint    ,replace(max(case when RetVal like '%K'  then RetVal end),'K',''))
		      ,Brand     = IIF(A.Type=1,null,max(case when RetSeq=2 then RetVal end))
		      ,Model     = replace(max(case when RetVal Like 'Model[0-9,A-Z]%' then RetVal end),'Model','')
		      ,SerialNum = replace(max(case when RetVal Like 'S/N[0-9,A-Z]%' then RetVal end),'S/N','')
		 From  (
                        Select RetSeq = row_number() over (Order By (Select null))
                              ,RetVal = ltrim(rtrim(B.i.value('(./text())[1]', 'varchar(max)')))
                        From  (Select x = Cast('<x>' + replace((Select replace(CleanString,' ','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
                        Cross Apply x.nodes('x') AS B(i)
		       ) B1
             ) C

Type	Description	Quantity	Name	Weight	Purity	Brand	Model	SerialNum
2	1LAPTOP APPLE 15.5" MODEL MACKBOOK PRO,S/N W80461WCAGX, WITH CHARGER	1	LAPTOP	null	null	APPLE	MACKBOOK	W80461WCAGX
1	1RING 2.3PW 14K	1	RING	2.3	14	null	null	null
2	DRILL RIOBY, MODEL D5521 S/N77720	1	DRILL	null	null	RIOBY	D5521	77720
2	TRIMMER TORO, MODEL 0242 S/N 66759	1	TRIMMER	null	null	TORO	0242	66759
2	CELL SAMSUNG NOTE3, MODEL SM-N900T S/N RV8F90YLZ9W	1	CELL	null	null	SAMSUNG	SM-N900T	RV8F90YLZ9W

Warning: Null value is eliminated by an aggregate or other SET operation.