Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
removing spaces between the numbers | Excel Discussion (Misc queries) | |||
Extra Spaces with copied data | Excel Discussion (Misc queries) | |||
removing unnecessary spaces from multiple cells | Excel Worksheet Functions | |||
Removing spaces in cells with data in it | Excel Discussion (Misc queries) | |||
Removing trailing spaces from cells ? | Excel Worksheet Functions |