Delete blank spaces

[ermis1975]

I try to delete all blanck spaces in a column. The column has one blank space in front of an number eg. (" 23423" to become "23423")
Thanks

[austenr]

in B1 enter this formula:

=trim(A1)

copy down. This will take out the spaces in the cell. Then copy the cells in column B, and Paste Special "Values Only" in column A.

[ermis1975]

Thanks!!

[austenr]

Did that solve your problem? If so please mark the thread solved

[ermis1975]

Hello. It didnt works. the number returns as it was eg. _234234, didnt become 234234
without the blank space _ .

[austenr]

Works on this end. This assumes a couple of things:

1. Your text number "_12345" is in A1
2. You type in this formula in B1: =trim(a1)
3. Your number should appear in B1 moved one character to the left and should now look like this: "12345"

[ermis1975]

I copy the blank space and I use the find and replace (remplace with nothing).
So the "blank space" disapears. If I do the same and put space to find it didnt replace anything. Maybe a secret character?

[TonyJollans]

Probably a hard space - char(160) - find/replace is your best bet.

[alimcpill]

yep, definitely sounds like non-breaking (hard) spaces. You can use =CODE(A1) to check the character code of the first character in a cell. If it's 160, it's a non-breaking space. Try this to get around it, yoinked from the Excel 2003 help docs (assuming you've got one of your numbers in cell A1,if not I'm sure you can edit the cell reference!):

=TRIM(SUBSTITUTE(A1,CHAR(160),CHAR(32)))

Microsoft's description:
Replaces each nonbreaking space character (Unicode value of 160) with a space character (ASCII value of 32) by using the SUBSTITUTE function, and then removes the leading and multiple embedded spaces from the string