13

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.

Improve this question
1
  • 1
    hey, do you want to split that string in 3 numbers or just to threat this as one number (in that case, " " is like a number separator of each x1000)?
    – kokbira
    Jul 8, 2011 at 13:19

11 Answers 11

Reset to default
10

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.

Improve this answer
3
  • 3
    +1 for considering non-breaking spaces. As an option, I also suggest using CHAR(160) to represent non-breaking spaces in your formula.
    – Ellesa
    Jul 9, 2011 at 4:52
  • You were correct. The spaces in between the numbers were non-breaking spaces. With your substitute I was able to remove them.
    – Mr Alpha
    Jul 10, 2011 at 11:34
  • 1
    he could simply copy the space in between and using it to match that whitespace
    – user8228
    Jul 10, 2011 at 11:48
6

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.

Improve this answer
2
  • 1
    if he wants to split the string in 3 numbers, it is not ok. but if he wants to threat that string as only one number, it is ok.
    – kokbira
    Jul 8, 2011 at 13:17
  • @kokbira He does not explicitly ask to split the numbers, as I interpreted "treat it as numbers" as meaning each cell would be a number. I can see where you are coming from in your interpretation, and that may very well be what he needs.
    – jonsca
    Jul 8, 2011 at 13:21
6

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," ","")
Improve this answer
8
  • Oh sure, go the "high-tech" route :) Just kidding, nice one.
    – jonsca
    Jul 8, 2011 at 12:16
  • This only removed the leading and trailing spaces, not the spaces in between the numbers.
    – Mr Alpha
    Jul 8, 2011 at 12:34
  • 1
    I did test it before posting and it correctly replaced all spaces. So I am not sure why it's not working for you.
    – Jane T
    Jul 8, 2011 at 13:08
  • if he wants to split the string in 3 numbers, it is not ok. but if he wants to threat that string as only one number, it is ok.
    – kokbira
    Jul 8, 2011 at 13:16
  • 2
    Weird. Usually, the formula above should work even with strings imported from a CSV. Try any of these and see if they work: =SUBSTITUTE(CLEAN(A2)), " ", "") or =SUBSTITUTE(A2,CHAR(160),"")
    – Ellesa
    Jul 9, 2011 at 4:39
2

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.

Improve this answer
1
  • if he wants to split the string in 3 numbers, it is ok. but if he wants to threat that string as only one number, it is not ok.
    – kokbira
    Jul 8, 2011 at 13:21
2

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

Improve this answer
1
  • if he wants to split the string in 3 numbers, it is ok. but if he wants to threat that string as only one number, it is not ok.
    – kokbira
    Jul 8, 2011 at 13:21
1

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.

Improve this answer
0

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.

Improve this answer
0

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

Improve this answer
0

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

Improve this answer
1
  • Welcome to Super User! Don't use all caps in your posts. No one wants to be screamed at.
    – Excellll
    Mar 14, 2016 at 14:57
0

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

Improve this answer
1
  • 2
    What do you mean by "a space is not a space"?
    – Toto
    Sep 20, 2017 at 10:36
0

Try this -

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

Improve this answer
1

Not the answer you're looking for? Browse other questions tagged or ask your own question.