Unwanted spaces in a text column

[ktyldesley]

Hi all,

I have a column containing text where many (but not all) of the entries have one or two keystroke spaces before the first word in the text entry. I want to remove those spaces without doing so manually.

For example, I may have a cell containing: "[space][space]auto" or "[space]auto".

What formula may I use to look for those spaces? The cell most often contains a string of several words so I can't simply delete all spaces in the column. Thanks!

Kevin

 

[VoG]

Try selecting the range then running the TrimAll macro http://dmcritchie.mvps.org/excel/join.htm

 

[Akashwani]

hi Kevin,

Welcome to MrExcel.

If you are happy using another column for a moment or two you could try using =TRIM() then do a Copy Paste Special Values.

Here's an example of =TRIM()

Excel Workbook
	A	B	C	D
1	Before	Length	After	Length
2	*Text *1	9	Text 1	6
3	Text 1	8	Text 1	6
4	*Text 1 *	11	Text 1	6
5	TEXT 1	7	TEXT 1	6
Sheet2

I have added =Len() (number of characters in a cell) so that you can see the difference.

Good luck.

Ak

 

[Maheshp]

Try
=TRIM(SUBSTITUTE(A2,CHAR(160),CHAR(32)))
http://office.microsoft.com/en-us/e...-beginning-and-end-of-a-cell-HP003056131.aspx