removing spaces after words

**Chemistification** · 10-21-2009, 08:36 AM

Hi,

Spaces after entries keep messing up my VLOOKUPS. I get a lot of data from other people and when they have entered the information in some of the entries have a space after the word which mess up the results of my vlookups. Is there an easy way of going through and removing spaces after a word? Not all the words have spaces after them.

Many thanks for your help

**omletto** · 10-21-2009, 08:40 AM

Hello, if there is just one space, you could highlight the column or row, press Ctrl H and serch for a space and replace with nothing. Hope this helps.

**darkyam** · 10-21-2009, 08:44 AM

If the entries are all only one word each, use =Substitute(A1," ","") and copy down, then copy all of these and paste over the original data as values and delete this column.

If they have multiple words, use the formula =IF(RIGHT(A1)=" ",LEFT(A1,LEN(A1)-1. This assumes there is only one space at the end of these words.

*Edit: Too slow, apparently. If only one space, omletto's suggestion is better. Silly me, I keep trying to do things with formulas.

**Chemistification** · 10-21-2009, 08:47 AM

Originally Posted by darkyam

If the entries are all only one word each, use =Substitute(A1," ","") and copy down, then copy all of these and paste over the original data as values and delete this column.

If they have multiple words, use the formula =IF(RIGHT(A1)=" ",LEFT(A1,LEN(A1)-1. This assumes there is only one space at the end of these words.

*Edit: Too slow, apparently. If only one space, omletto's suggestion is better. Silly me, I keep trying to do things with formulas.

Still tho like that second one for if i have two words. Both of these should easily help. Thanks both