TRIM not trimming
I have thousands of rows of data that I have used the "Text to Columns"
function on so that they are now two columns instead of one (text in A, numbers in B). However, there are leading and trailing spaces in both columns, and when I used the =TRIM function to remove them, it doesn't work. Even when I use TRIM in column D, then copy that value into Notepad, I can still see the spaces. What's wrong? -- Thanks, -Kim |
TRIM not trimming
If you are using data from the web there might be non space html characters
http://www.mvps.org/dmcritchie/excel/join.htm#trimall is a macro that will clean these here's how to install macros http://www.mvps.org/dmcritchie/excel...la.htm#install -- Regards, Peo Sjoblom "Kim" wrote in message ... I have thousands of rows of data that I have used the "Text to Columns" function on so that they are now two columns instead of one (text in A, numbers in B). However, there are leading and trailing spaces in both columns, and when I used the =TRIM function to remove them, it doesn't work. Even when I use TRIM in column D, then copy that value into Notepad, I can still see the spaces. What's wrong? -- Thanks, -Kim |
TRIM not trimming
If you are using data from the web there might be non space html characters
http://www.mvps.org/dmcritchie/excel/join.htm#trimall is a macro that will clean these here's how to install macros http://www.mvps.org/dmcritchie/excel...la.htm#install -- Regards, Peo Sjoblom "Kim" wrote in message ... I have thousands of rows of data that I have used the "Text to Columns" function on so that they are now two columns instead of one (text in A, numbers in B). However, there are leading and trailing spaces in both columns, and when I used the =TRIM function to remove them, it doesn't work. Even when I use TRIM in column D, then copy that value into Notepad, I can still see the spaces. What's wrong? -- Thanks, -Kim |
TRIM not trimming
Kim wrote...
.... However, there are leading and trailing spaces in both columns, and when I used the =TRIM function to remove them, it doesn't work. Even when I use TRIM in column D, then copy that value into Notepad, I can still see the spaces. .... I'd guess your cell data comes from an HTML source, and it contains nonbreaking spaces (decimal character code 160). You need to replace them with ASCII/breaking spaces, then apply TRIM. =TRIM(SUBSTITUTE(x,CHAR(160)," ")) |
TRIM not trimming
Kim wrote...
.... However, there are leading and trailing spaces in both columns, and when I used the =TRIM function to remove them, it doesn't work. Even when I use TRIM in column D, then copy that value into Notepad, I can still see the spaces. .... I'd guess your cell data comes from an HTML source, and it contains nonbreaking spaces (decimal character code 160). You need to replace them with ASCII/breaking spaces, then apply TRIM. =TRIM(SUBSTITUTE(x,CHAR(160)," ")) |
TRIM not trimming
if there are still apperant spaces you can use code(mid(...)) to identify the
code to use in the char() formula "Harlan Grove" wrote: Kim wrote... .... However, there are leading and trailing spaces in both columns, and when I used the =TRIM function to remove them, it doesn't work. Even when I use TRIM in column D, then copy that value into Notepad, I can still see the spaces. .... I'd guess your cell data comes from an HTML source, and it contains nonbreaking spaces (decimal character code 160). You need to replace them with ASCII/breaking spaces, then apply TRIM. =TRIM(SUBSTITUTE(x,CHAR(160)," ")) |
TRIM not trimming
Try using CLEAN() alongside TRIM().
On Aug 8, 6:22 pm, Kim wrote: I have thousands of rows of data that I have used the "Text to Columns" function on so that they are now two columns instead of one (text in A, numbers in B). However, there are leading and trailing spaces in both columns, and when I used the =TRIM function to remove them, it doesn't work. Even when I use TRIM in column D, then copy that value into Notepad, I can still see the spaces. What's wrong? -- Thanks, -Kim |
TRIM not trimming
It will not work for invisible html characters, just non printable
characters -- Regards, Peo Sjoblom "ilia" wrote in message oups.com... Try using CLEAN() alongside TRIM(). On Aug 8, 6:22 pm, Kim wrote: I have thousands of rows of data that I have used the "Text to Columns" function on so that they are now two columns instead of one (text in A, numbers in B). However, there are leading and trailing spaces in both columns, and when I used the =TRIM function to remove them, it doesn't work. Even when I use TRIM in column D, then copy that value into Notepad, I can still see the spaces. What's wrong? -- Thanks, -Kim |
All times are GMT +1. The time now is 10:43 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com