Removing spaces from cell with numbers

[danhendo888]

Tried formatting the column to Text, Numbers, General, Currency but cannot seem to remove the spaces
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]https://ibb.co/kQXF4GV[/FONT]
Both columns are aligned to the left and have spaces in front of them.
[/FONT]
[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J2[/TH]
[TD="align: left"]=TRIM(I2)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J3[/TH]
[TD="align: left"]=CLEAN(I3)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J4[/TH]
[TD="align: left"]=SUBSTITUTE(I4," ","")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Any idea how to do it?

 

[sandy666]

post a link to the shared excel file with representative example not a picture

 

[Fluff]

Maybe
=SUBSTITUTE(I4,char(160),"")

 

[danhendo888]

=SUBSTITUTE(I4,char(160),"")

Thank you Fluff, it worked
When I used the formula, it created one space on either side of the number.
I then did a search and replaced the spaces with nothing.

FWIW FYI the spreadsheet in question
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]https://1drv.ms/x/s!AvjBsEPEq12ngSXcAbrkSBtIo_dl?e=K9Qphn[/FONT]

 

[Fluff]

You're welcome & thanks for the feedback.
However that formula does not add any spaces.

 

[sandy666]

or

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#4472C4]Invoice Amount (Net Price) [/td][td][/td][td=bgcolor:#70AD47]Invoice Amount (Net Price) [/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#D9E1F2] 176.80[/td][td][/td][td=bgcolor:#E2EFDA]

176.8​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td] 137.60[/td][td][/td][td]

137.6​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#D9E1F2] 406.59 [/td][td][/td][td=bgcolor:#E2EFDA]

406.59​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td] 175.22 [/td][td][/td][td]

175.22​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#D9E1F2] 300.00 [/td][td][/td][td=bgcolor:#E2EFDA]

300​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td] 265.75 [/td][td][/td][td]

265.75​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#D9E1F2] 94.75 [/td][td][/td][td=bgcolor:#E2EFDA]

94.75​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td] 235.00 [/td][td][/td][td]

235​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#D9E1F2] 658.27 [/td][td][/td][td=bgcolor:#E2EFDA]

658.27​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td] 245.25 [/td][td][/td][td]

245.25​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#D9E1F2] 908.06 [/td][td][/td][td=bgcolor:#E2EFDA]

908.06​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td] 673.99 [/td][td][/td][td]

673.99​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#D9E1F2] 749.45 [/td][td][/td][td=bgcolor:#E2EFDA]

749.45​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td] 204.80 [/td][td][/td][td]

204.8​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#D9E1F2] 523.78 [/td][td][/td][td=bgcolor:#E2EFDA]

523.78​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td] 751.09 [/td][td][/td][td]

751.09​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#D9E1F2] 334.22 [/td][td][/td][td=bgcolor:#E2EFDA]

334.22​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td] 267.19 [/td][td][/td][td]

267.19​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#D9E1F2] 487.03 [/td][td][/td][td=bgcolor:#E2EFDA]

487.03​

[/td][/tr]
[/table]

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Invoice Amount (Net Price) ", type number}})
in
    Type[/SIZE]