Csdrhrt

Just do a search and replace over a group of cells you have selected, select your column and go to Home and Find and Select. Search for a space, and replace it with nothing.

Alternately if you want to retain the originally formatted number you can use the substitute function to provide a revised version of the string in another cell:

=SUBSTITUTE(A2," ","")

It sounds like you want to extract the numbers and put them in to separate columns. You can use the "Text to Columns" command, which is located on the Data tab (in Excel 2007). This walks you through a series of dialog boxes letting you specify how the fields are delimited and how you want the numbers formatted.

This solution is to split 1 string with 3 numbers separated by spaces in 3 other numbers.

For didactic Purposes, let's consider:

     |    A    |    B    |    C    |    D    |    E    |

_____|_________|_________|_________|_________|_________|

     |         |         |         |         |         |

  1  |123 45 67|         |         |         |         |

_____|_________|_________|_________|_________|_________|

     |         |         |         |         |         |

  2  |         |         |         |         |         |

_____|_________|_________|_________|_________|_________|

     |         |         |         |         |         |

  3  |         |         |         |         |         |

_____|_________|_________|_________|_________|_________|

     |         |         |         |         |         |

  4  |         |         |         |         |         |

_____|_________|_________|_________|_________|_________|

So we can do that to help with next formulas:

For B1, where we start our search in A1 string - first character: =1

For C1, where is the first space in A1: =SEARCH(" ";A1;B1)

For D1, where is the second space in A1: =SEARCH(" ";A1;C1)

For E1, the length of string in A1: =LEN(A1)

And then what do you want:

For B2, to get first number: =SUBSTITUTE(A1;C1;C3-C1+1;"")

For C2, to get second number: =EXTRACT(A1;C1+1;D1-C1-1)

For D2, to get third number: =SUBSTITUTE(A1;B1;C2;"")

If you want, you can eliminate auxiliar formulas in B1:E1 including their contents in formulas in B2:D2

I had the same problem, a space occured as soon as my data had a 1000 plus value (so all numbers 1000+ looked like 3 333,00.
I discovered that it was indeed the ALT+0160 character (I discoverd this by copying and pasting it word.

Quick fix to remove this "invisble char" is the following:

1. Select a cell where the space occurs


2. Select and copy ONLY the "space"


3. Select entire worksheet (Ctrl +A)


4. Start the find function (Ctrl + F)


5. Go to the "replace" tab.


6. "Find what" -> Paste your "space", "Replace with" -> Leave empty


7. Select "Replace all"

Now all your data should be without spaces, and excel should see all data as Numbers.

Quick to remove the "invisible char" at the "blank cell" in Worksheet as follows.

1. Select a blank cell where the space occurs


2. Copy the blank cell (Ctrl + C)


3. Start the find function (Ctrl + F)


4. Go to the "replace" tab.


5. "Find what" -> Paste your "space", "Replace with" -> Leave empty


6. Click "Option"


7. Tick "Match entire cell contents"


8. Select "Replace all"

P.S. The useful information is without affected.

just copy your data into a .txt file and open the .txt file with excel, this will separate each column correctly into the worksheet...

Just use the text to column feature in data group .. and split your data using ""space"" option use ""concatenate"" to combine these number without spaces.

For Example:

32 445 423

use "text to column"

it will return

32|445|423

use concatenate formula, that will return your required format 32445423

Sometimes a space in excel, are not a spaces, try to copy that "space" and pasted in replace dialog instead " ".

Try this -

=VALUE(SUBSTITUTE(A2,CHAR(160),""))