Removal of 100's of spacing in numbers

C

cas

I want to be able to use the replace command, but it won’t find the spaces
for me.

When I copy tables with numbers from various sources, I get spaces inside
and after the numbers. E.g. “1 234 “

I know the use of SUBSTITUTE and other functions, but it is not an effective
way of working when there are so many columns. I have tried to change the
cell format to text, but still no luck in finding the spaces with the replace
command.
 
F

Faraz Ahmed Qureshi

Might be some other character.
Place your cursor between 1 and 2 and copy
Select the columns with numbers
Press Ctrl+H
In the Find box press Ctrl+V for the space like character
Leave the Replace With box blank
and press Replace All

Best of luck.
 
L

Luke M

It may be some other non-printable character, such as CHAR(160)
You can either try copying the space from your data and then do a
find&replace on that, or you could try and determine what exact character it
is via:
=CODE(MID(A2,2,1))
If it's a true space, it should be code 32.
Check out the XL help topic on CLEAN for more info on non-printable
characters.
 
C

cas

You are totally right. I copied it to Word where I could see that it was
"hard spacing" (don't know the correct word for it in English). A bit ashamed
that I didn’t try to copy the character to the replace-box. Maybe a too easy
solution :)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top