Removing random extra spaces
 

I have a column for comments
there are 900 rows (A1:A900)

this data is being imported from another source and some users have
extra spaces that I want to remove and place in (B1:B900)...I also want
to format it so that it is converted from all caps to all lowercase.

For example:

A1 reads "INT IN A FREE QUOTE ON 50000 CASH FOR DEBT
CONSOLIDATION"

B2 reads "int in a free quote on 50000 cash for debt consolidation"


--
jermsalerms
------------------------------------------------------------------------
jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167
View this thread: http://www.excelforum.com/showthread...hreadid=502511

Removing random extra spaces
 

B1=trim(lower(a1))


--
jermsalerms
------------------------------------------------------------------------
jermsalerms's Profile: http://www.excelforum.com/member.php...o&userid=30167
View this thread: http://www.excelforum.com/showthread...hreadid=502511

Removing random extra spaces
 

In cells B1:B900, put the following formula:-

=TRIM(LOWER(A1))


--
Gary Brown
------------------------------------------------------------------------
Gary Brown's Profile: http://www.excelforum.com/member.php...o&userid=17084
View this thread: http://www.excelforum.com/showthread...hreadid=502511

Removing random extra spaces
 

In cell B1, use the formula =LOWER(A1) to convert the value of A1 to
all lower case. Copy that formula down thru B900. Then copy column B,
and Paste Special as Values onto column B, converting the formulas to
text. Highlight column B; search for " " (two spaces, but don't use
the quotes) and replace with " " (a single space, no quotes). Repeat
the search and replace until you get the "can't find anything to
replace" message.