CODEWITHSUNDEEP
stringgoogle-sheetstrim
MalcL
MalcL

Reputation: 129

How to remove trailing spaces from text

In a Google spreadsheet I have some data imported from a .csv file which is loc A1 = 123.4 followed by 2 spaces

I want to use the numeric value but the spreadsheet refuses to recognize the string as a number.

The obvious answer is substitute(A1;" ";"") but this does not work!!. Nor do any of the other string search commands.

Am I going insane?

I am using a Mac running 10.4.7 and chrome

Upvotes: 1

Views: 2192

Answers (3)

MalcL
MalcL

Reputation: 129

Thanks @kaushai and @davidzemens

I have edited line 22 of the sheet.

It shows that =Substitute(A4,CHAR(160), "") is not numeric

however =Substitute(A4,CHAR(160), "")*1 is numeric

and =value(Substitute(A4,CHAR(160), "")) is numeric

Upvotes: 0

Kaushal
Kaushal

Reputation: 675

Not sure if you have already solved this... try this...this seems to be working

=Value(Substitute(A13,CHAR(160), ""))

or

=Substitute(A13,CHAR(160), "")*1

Upvotes: 1

David Zemens
David Zemens

Reputation: 53623

OK. I've examined this in Excel (where I'm more handy with VBA/etc) and these are not ordinary "spaces" in your cell, they are actually non-breaking spaces, an ascii chr value of 160 (ordinary space is Chr(32)).

Try this formula to replace the non-breaking space character with a null string:

=SUBSTITUTE(A13,CHAR(160),"")

Excel has a function called Clean() which removes non-printing characters like this, but I do not see this function in Google Docs.

Upvotes: 1

Related Questions