Odd numbers

[Tim]

Hello All,

I have a data that comes from the source with some sort
of a weird format. There seems to be a bunch of spaces
before the number. When you look at the value in the
formula bar, you see a gap before the number starts. I
can go in and manually delete the space and the number
becomes usable. Is there some other way to accomplish
this?

I can also use a MID formula then multiply the result by
1 to make the number usable.....I just do not know where
to start the mid. It varies with or by the number of
characters. Any ideas would be greatly appreciated.
Thanks

 

[Harlan Grove]

Tim wrote...

I have a data that comes from the source with some sort
of a weird format. There seems to be a bunch of spaces
before the number. When you look at the value in the
formula bar, you see a gap before the number starts. I
can go in and manually delete the space and the number
becomes usable. Is there some other way to accomplish
this?

....

First select the range containing these numbers. Then run the menu
command Edit > Replace, enter a space for the 'Find what' entry, make
sure the 'Replace with' entry contains nothing, then Replace All.

 

[Peo Sjoblom]

Edit>replace, replace space with nothing


Regards,

Peo Sjoblom

 

[Tim]

Tried the replace function with no luck, it replaces the
space at the end of the number oand not at the
beginning. The only thing that seems to work is to
maually remove the space in the formula bar.

thanks

 

[Harlan Grove]

Tim wrote...

Tried the replace function with no luck, it replaces the
space at the end of the number oand not at the
beginning. The only thing that seems to work is to
maually remove the space in the formula bar.

....

In that case these are almost certainly nonbreaking spaces (decimal
character code 160) rather than normal/ASCII spaces (decimal character
code 32). Instead of typing a space in the 'Find what' entry, clear
that entry, then hold down the [Alt] key and on the number keypad type
in sequence 0, 1, 6, 0 and release the [Alt] key. That should enter a
character that looks like a space. Then replace all with nothing.