Excel formula to remove space between words in a cell

Question

I've a huge data in excel file.

For eg: say i've a word like paul son,i've to make it as paulson.

input:paul son output:paulson.

In some cells ,i've data like mic-li,when this type of words come,it should not replace any thing,it should only remove spaces in between words.

vulkanino · Accepted Answer · 2015-09-08T10:09:16.327

57

Suppose the data is in the B column, write in the C column the formula:

=SUBSTITUTE(B1," ","")

Copy&Paste the formula in the whole C column.

edit: using commas or semicolons as parameters separator depends on your regional settings (I have to use the semicolons). This is weird I think. Thanks to @tocallaghan and @pablete for pointing this out.

edited Sep 08 '15 at 10:09

answered Feb 08 '12 at 09:55

vulkanino

9,074
7
44
71

3

as @Richard's answer, use comma's not semi-colons – tocallaghan Feb 11 '14 at 03:41
@tocallaghan it depends on your regional configuration (I mean the comma vs semi-colons) – pablete Jun 05 '14 at 14:15

score 37 · Answer 2 · edited Jan 20 '13 at 23:31

37

It is SUBSTITUTE(B1," ",""), not REPLACE(xx;xx;xx).

edited Jan 20 '13 at 23:31

Olle Sjögren

5,315
3
31
51

answered Jan 20 '13 at 23:08

Richard

371
1
3
3

3

This is more of a comment than an answer, so it should be posted as a comment to the answer it concerns (when you have the rep to do so...) – Olle Sjögren Jan 20 '13 at 23:28

score 6 · Answer 3 · answered Apr 20 '15 at 04:55

6

Steps (1) Just Select your range, rows or column or array , (2) Press ctrl+H , (3 a) then in the find type a space (3 b) in the replace do not enter anything, (4)then just click on replace all..... you are done.

answered Apr 20 '15 at 04:55

kvn4kevin

61
1
2

For an excel illiterate like me, this was way too easier. – Saumil May 21 '19 at 15:02

score 0 · Answer 4 · answered Dec 10 '21 at 01:18

Just wanted to add on to vulkanino's answer... now 10 years later ha. This is what I was looking for maybe someone else is too. You can also build on multiple =SUBSTITUTE() functions.

Here is what I used to format a list of names for Active Directory accounts. B2 is where the name would go. I wanted to change the space between the First name and last to a period and omit any hyphens from names.

=SUBSTITUTE(SUBSTITUTE(B2, " ", "."), "-", "")

For example the line above would look like this:

First Last-Name => First.LastName

, "-", "") This part removes the hyphen.

(B2, " ", ".") This part selects the cell to modify and changes empty space to a period.

=SUBSTITUTE(SUBSTITUTE(...)...) I used two substitute functions one inside the other.

Excel formula to remove space between words in a cell

4 Answers4

Linked