how do I get a simple #"123" from 1 2 3 ? Spaces between numbers

[Chris]

Visa put SPACES between all the numbers. (e.g. 1 2 3 . 4 5 )


How do I get back to a 123.45?

 

[Fred Smith]

Try:

=--substitute(a1," ","")

Regards,
Fred.

 

[OssieMac]

Hi Chris,

Back up up your data before you do this incase spaces get removed in areas
that you do not want them removed.

Select the data with the unwanted spaces.
Select Replace (Under menu item Edit in versions up to xl2003 and in far
right of home ribbon in xl2007)

Press space once in the Find field and do not put anything in the replace
field and then click Replace All.

 

[Ashish Mathur]

Hi,

Try this =SUBSTITUTE(E10," ","")

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[OssieMac]

Hi again Chris,

If my previous post does not work then the spaces maybe non printable
characters that are not really spaces. Try using Clean function.

You will need a helper column beside the column of data and place the Clean
function in the helper column.
Example:

=CLEAN(A2)

Copy the function down to bottom of data and then select the column and
Copy -> Past special -> Values.

 

[MartinW]

Hi Chris,

The simplest way is to use Edit>Replace.

Select all of your data first, then go to Edit>Replace.
Find what: put a space in the box.
Replace with: leave this box empty.
Then click Replace All.

If this data came from the net you may have non-breaking spaces as well.
To get rid of them repeat the same process but use a non-breaking space in
place
of the normal space.
To type a non-breaking space hold down the Alt key and type 0160 on the
number pad only, and then let go of the Alt key.

If you do this as a two step operation don't forget to delete the normal
space
before you start the next step.

You may also want to look in the help file for the TRIM and CLEAN functions.


HTH
Martin

 

[Shane Devenshire]

Hi,

As OssieMac pointed out the spaces may not be spacebars so using either a
formula or the Replace command may fail. If they don't work then try this:

Suppose the number is in cell A1
1 2 3 . 4 5
in B1 enter the formula
=CODE(MID(A1,2,1))
This will return the character code of the first space as a number such as
178.
In the substitute formula use
=SUBSTITUTE(A1,CHAR(178),"")
for the Replace command highlight the space in the cell and copy it (just
the space), choose the Edit, Replace command and click in the Find what box
and press Ctrl V. Leave the Replace with box empty and choose Replace All.