Remove leading zeroes and trailing space

Hi,

I have a cell that contains a number that is text.

It has leading zeroes and a space after the last digit.

"0011277370 "

I would like to convert this to a number without the leading zeroes and the trailing space.

There may be more, less or no leading zeroes in the numbers and may or may not have a trailing space.

If a number has no leading zeroes and no trailing space then it should remain untouched.

Originally Posted by humdingaling

text to column to make it general than do it again to make it back to text?

I don't think I understand the steps, text to column and do it again?

Originally Posted by bebo021999

Assuming A1 contains number-stored-as- text
Try:
B1=A1+0

Or: Add 0 into those cells:
Copy a blank cell
Choose range with number-stored-as text
Paste special/Operation:Add/OK

When I add zero to the text number I end up with #VALUE

Attached is a small sample

Originally Posted by sandy666

you can try with =--TRIM(A1) where A1 = 0011277370 - (with space on the end)

Still end up with #VALUE

Originally Posted by sandy666

btw. in your example on the end of string is not a space

so try: =--TRIM(SUBSTITUTE(A2,CHAR(160),""))

Great that worked. Can you tell me what the 2 hyphens after the equals does please?

re-read post above

changing text number to number number (same as +0 or *1)

If that takes care of your original question, & to say Thanks, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.
If you did it already - ignore it.
Thank you.

Thanks guys (and gals).

@bebo
"" = empty string
nothing is null

thanks for rep(s) (bebo & kerspash) and mark thread solved