I have a dataset that I've copy pasted into Excel and some numbers have spaces between them. This is subset of a column in the spreadsheet:

As you can see in the 3rd, 4th, 5th, 9th and 10th rows there's some space between the characters that I want to delete.

Are there any good formulas here for this? I've tried the TRIM-function a couple times but it's only useable for strings. And a complementary note is that they are all treated as generals.

CodePudding user response：

1. Highlight the range that you want to modify
2. CTRL F
3. Click Replace
4. "Find what: " (put a blank space here so it searches for spaces)
5. "Replace with: " (leave this blank)
6. Click Replace all, and the selected range should search for blank spaces and replace them with nothing

CodePudding user response：

There are two functions you can use for replacing a character in a string: Replace() and Substitute():

• Replace() can be used when you know where you want to replace whatever.
• Substitute() can be used if you know exactly what you want to replace anywhere.

So, in this case:

=SUBSTITUTE(C2," ","")

CodePudding user response：

=NUMBERVALUE(SUBSTITUTE(A1:A10," ","")) replaces the space and returns a number

CodePudding user response：

yea firstly select the column and you can use 'Text to Column' which is under the 'Data' menu bar.

enter image description here