Eliminate all spaces in a text string

Luis Ah Hoy Jr

New Member
Joined
Mar 15, 2017
Messages
5
A fellow worker sent me a spreadsheet full of cells formatted in such a way that it makes it difficult to remove some of the spaces.

For testing purposes, here's the file:
Uploadfiles.io - remove all spaces.xlsx

In this example, I have this text string in cell A1: " 6403 "
I want to remove all spaces so that it only remains as: "6403".

In this case, the TRIM() and the SUBSTITUTE() methods only work to eliminate the spaces after the number, but they both fail to eliminate the spaces before it.

Any idea?
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Have you tried the clean function?
=Clean()
 
Upvote 0
Hi, your spaces are not normal spaces - you can try like this to remove them.

=0+SUBSTITUTE(A1,CHAR(160),"")

Or you can do Find > Replace in the "Find what" field hold the ALT Key and type 0160 on the keyboard number pad - leave replace with blank.
 
Upvote 0
That worked, FormR. Many thanks. :)

There's this other method that did the trick for me, though it's a bit more complex than yours:
=TRIM(SUBSTITUTE(A1;CHAR(160);CHAR(32)))

They probably provide different results when applied in some other case(s). But in this particular problem they worked equally well.
 
Upvote 0
You may need to delete all blanks, like in a formula chain. To do this, you can create a helper column and enter the formula: =SUBSTITUTE(A1," ","")
Here A1 is the first cell of the column with numbers or words where all spaces must be deleted.
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top