How to remove white space from a number

Question

I have a column of number with white spaces in them. Like this: "32 445 423". How do I get rid of all the white space to get excel to treat it as numbers rather than strings?

EDIT: Let me fill in some additional information that has come to light. My goal is to turn "32 445 423" into a 32445423. If I write in the string "32 445 423" into excel and use =SUBSTITUTE(A1;" ";"") it removes the spaces just fine. But the numbers I have are imported from a csv file. When running SUBSTITUTE on the imported numbers only leading and trailing spaces are removed.

Answer 1

I believe your imported data includes non-breaking spaces instead of standard spaces. Hence, the formula =SUBSTITUTE(A1," ","") does not work. Instead of using the space bar to type the " " in the SUBSTITUTE formula, try a non-breaking space. You can select this in the Character Map or use keystroke Alt+0160.

EDIT:

Since substituting regular spaces successfully removed the leading and trailing spaces, you should use nested Substitute formulas: one to sub the regular spaces and one to remove the no-break spaces.

=SUBSTITUTE(SUBSTITUTE(A1," ","")," ","")

You can copy this formula to try it in your workbook.

Answer 2

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.

Answer 3

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," ","")

Answer 4

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.

Answer 5

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

Answer 6

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.

Answer 7

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

Answer 8

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

Answer 9

Try this -

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

Answer 10

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.

Answer 11

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