CODEWITHSUNDEEP
excel-formulaexcel-2010
user1180001
user1180001

Reputation:

Excel formula to remove space between words in a cell

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.

Upvotes: 31

Views: 240747

Answers (4)

R08
R08

Reputation: 1

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.

Upvotes: 0

vulkanino
vulkanino

Reputation: 9124

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.

Upvotes: 59

kvn4kevin
kvn4kevin

Reputation: 71

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.

Upvotes: 7

Richard
Richard

Reputation: 371

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

Upvotes: 37

Related Questions