CODEWITHSUNDEEP
arraysgoogle-sheetsgoogle-sheets-formulanumber-formattingarray-formulas
Simon Breton
Simon Breton

Reputation: 2876

removing white space in numbers when trim and clear doesn't work

I have a spreadsheet filled with numbers like this:

2 539.39
1 277.36
2 881.42
2 559.00

Google sheet doesn't identify them as a number until I remove the space:

2539.39
1277.36
2881.42
2559.00

however I've tried trim, clear and replace but I'm not able to remove this space. It is probably something else than a space.

What should I do?

Upvotes: 1

Views: 1475

Answers (2)

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(IF(A1:A="",,SUBSTITUTE(A1:A, " ", )*1))

0

but if you want that space and still have number you can format it like:

0

Upvotes: 2

CodeCamper
CodeCamper

Reputation: 6984

=SUBSTITUTE(A1," ",)

works fine, trim and clear do not remove single spaces in the middle of a string. If your space is always going to be the second character you can use replace as follows:

=replace(A1,2,1,"")

However replace will not remove all spaces anywhere like the above substitute method.

Upvotes: 2

Related Questions