I found that you added CASTING with BIGINT. Thanks.
The revised script is here:-
********************************************************************
set nocount on
CREATE TABLE #tempList (Files VARCHAR(500))
DECLARE @result int,@cmd sysname,@cmd3 sysname
DECLARE @var sysname = 'C:\Users\'
SET @cmd3 = 'DIR ' + @var
INSERT INTO #tempList
EXEC MASTER..XP_CMDSHELL @cmd3
--delete all directories
DELETE #tempList WHERE Files LIKE '%<dir>%'
--delete all informational messages
DELETE #tempList WHERE Files LIKE ' %'
--delete the null values
DELETE #tempList WHERE Files IS NULL
--get rid of dateinfo
UPDATE #tempList SET files =RIGHT(files,(LEN(files)-20))
--get rid of leading & Trailingspaces
UPDATE #tempList SET files = RTRIM(LTRIM(files))
-- split data into size and filename
SELECT ROW_NUMBER() OVER ( ORDER BY Files DESC ) AS Row
, LEFT(Files, PATINDEX('% %', Files)) AS Size_Bytes --File size in bytes
, RIGHT(Files, LEN(Files) - PATINDEX('% %', Files)) AS FileName
, CAST(REPLACE(LEFT(Files, PATINDEX('% %', Files)), ',', '') AS BIGINT) / 1024 AS FileSize_KB --File size in KB
, CAST(REPLACE(LEFT(Files, PATINDEX('% %', Files)), ',', '') AS BIGINT)/ 1024 / 1024 AS FileSize_MB --File size in MB
FROM #tempList
WHERE LEFT(Files, PATINDEX('% %', Files)) LIKE '%,%';
DRop table #tempList
set nocount off
Thanks.