Remove blank spaces from cells where number varies

[Legacy 177768]

We have some spreadsheets where people have been using blank spaces to force text to appear to be on a next line (instead of doing an Alt+Enter line break).

I have to copy the contents of these spreadsheets up to a SharePoint List, and when I do, the extra spaces (in Column I, for example) throw off the sizing in that column in SharePoint. So, I need to clean up these cells *before* I do my copy and paste to the online List.

How to I search through cells in a column in Excel to remove various lengths of empty spaces? One cell might have groups of text with 20 spaces between them, and another cell might have some text with 5 spaces between.

It would seem I need to remove anything more than a single space down to 2 spaces, and then insert a line break (Alt+Enter) in those places, so the text break is moved to a new line within the cell.

Thanks for your help!

 

[gledister]

tried with =trim(text)?!

 

[T. Valko]

We have some spreadsheets where people have been using blank spaces to force text to appear to be on a next line (instead of doing an Alt+Enter line break).

I have to copy the contents of these spreadsheets up to a SharePoint List, and when I do, the extra spaces (in Column I, for example) throw off the sizing in that column in SharePoint. So, I need to clean up these cells *before* I do my copy and paste to the online List.

How to I search through cells in a column in Excel to remove various lengths of empty spaces? One cell might have groups of text with 20 spaces between them, and another cell might have some text with 5 spaces between.

It would seem I need to remove anything more than a single space down to 2 spaces, and then insert a line break (Alt+Enter) in those places, so the text break is moved to a new line within the cell.

Thanks for your help!

The macro at this website will remove all leading/trailing and multiple interspersed char 32 space characters. It will also remove and/or convert char 160 non breaking spaces into standard char 32 space characters.
It will work on text or numbers and the numbers will be converted to true numeric numbers.

I use this macro dozens of times every single day! It is a real time saver.

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

 

[Legacy 177768]

Thanks guys, that should get me fixed up nicely

 

[T. Valko]

Thanks guys, that should get me fixed up nicely

You're welcome. Thanks for the feedback!