Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with TRIM function in Excel
I am trying to write a macro using the Trim function.
I imported data in from another source. Column A contains the name in a "Last, First" format. Also, that data comes into Excel as a hyperlink to the original software. (I don't need a hyperlink for my needs -- but that is the way I get the info) I used the Data--Columns to Text function to separte the last and first names into different columns. The First name has a space in front of it. Later, there will be 400+ rows in this worksheet so I wrote a macro to loop through the data and TRIM the spaces out of the FirstName. But...nothing happens. The macro loops through my test data but the space is always there. I looked at the Ascii representation and the space that comes from the imported file is a 160. I thought maybe it does not like that definition for a space so I typed in a First Name putting a space at the front. When I checked the Ascii defintion for that it was a 32. I ran the macro on both that and it still did not remove the space. I have also tried LTRIM. Here is an example of my code (execpt it is in a loop) Dim strtext As String (I have also tried it as a Variant) strtext = FirstName MsgBox "1 - strtext: " & strtext (this lets me see the macro is running) LTrim ([strtext]) FirstName.Offset(0, 1) = strtext (moving the trimmed data to a new column) The name shows up in column C but it still has the space. Does anyone have any ideas? Should be using or not using a specific option? I am sure the answer is something simple -- but I am stumped. Thanks, Julie |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with TRIM function in Excel
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with TRIM function in Excel
Julie
This may help, I selected A1:A20 which all contained first names with spaces preceding them. I then used the following code: Sub RemoveSpace() Dim FirstName As Range For Each FirstName In Selection FirstName.Select FirstName = LTrim(FirstName) Next FirstName End Sub Hope it helps. "Julie Beth" wrote: I am trying to write a macro using the Trim function. I imported data in from another source. Column A contains the name in a "Last, First" format. Also, that data comes into Excel as a hyperlink to the original software. (I don't need a hyperlink for my needs -- but that is the way I get the info) I used the Data--Columns to Text function to separte the last and first names into different columns. The First name has a space in front of it. Later, there will be 400+ rows in this worksheet so I wrote a macro to loop through the data and TRIM the spaces out of the FirstName. But...nothing happens. The macro loops through my test data but the space is always there. I looked at the Ascii representation and the space that comes from the imported file is a 160. I thought maybe it does not like that definition for a space so I typed in a First Name putting a space at the front. When I checked the Ascii defintion for that it was a 32. I ran the macro on both that and it still did not remove the space. I have also tried LTRIM. Here is an example of my code (execpt it is in a loop) Dim strtext As String (I have also tried it as a Variant) strtext = FirstName MsgBox "1 - strtext: " & strtext (this lets me see the macro is running) LTrim ([strtext]) FirstName.Offset(0, 1) = strtext (moving the trimmed data to a new column) The name shows up in column C but it still has the space. Does anyone have any ideas? Should be using or not using a specific option? I am sure the answer is something simple -- but I am stumped. Thanks, Julie |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with TRIM function in Excel
Thank you to all who responded. I started with the David McRitchie solution
and it worked great! Thank you again -- you save me a ton of time and frustration Julie "Norman Jones" wrote: Hi Julie, See David McRitchie's TrimAll sub at: http://www.mvps.org/dmcritchie/excel/join.htm - trimall --- Regards, Norman "Julie Beth" <Julie wrote in message ... I am trying to write a macro using the Trim function. I imported data in from another source. Column A contains the name in a "Last, First" format. Also, that data comes into Excel as a hyperlink to the original software. (I don't need a hyperlink for my needs -- but that is the way I get the info) I used the Data--Columns to Text function to separte the last and first names into different columns. The First name has a space in front of it. Later, there will be 400+ rows in this worksheet so I wrote a macro to loop through the data and TRIM the spaces out of the FirstName. But...nothing happens. The macro loops through my test data but the space is always there. I looked at the Ascii representation and the space that comes from the imported file is a 160. I thought maybe it does not like that definition for a space so I typed in a First Name putting a space at the front. When I checked the Ascii defintion for that it was a 32. I ran the macro on both that and it still did not remove the space. I have also tried LTRIM. Here is an example of my code (execpt it is in a loop) Dim strtext As String (I have also tried it as a Variant) strtext = FirstName MsgBox "1 - strtext: " & strtext (this lets me see the macro is running) LTrim ([strtext]) FirstName.Offset(0, 1) = strtext (moving the trimmed data to a new column) The name shows up in column C but it still has the space. Does anyone have any ideas? Should be using or not using a specific option? I am sure the answer is something simple -- but I am stumped. Thanks, Julie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Function =Trim() | Excel Worksheet Functions | |||
Trim function to remove blank spaces in Excel | Excel Discussion (Misc queries) | |||
Trim Function | Excel Worksheet Functions | |||
Trim, Len, Left causing problem | Excel Programming | |||
Trim function in Excel Doesn't work for certain cells | Excel Programming |