Problem with TRIM function in Excel

[Guest]

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

 

[Norman Jones]

Hi Julie,

See David McRitchie's TrimAll sub at:

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

 

[Gary Keramidas]

how about this

=MID(A1,2,(LEN(A1)-1))

 

[Guest]

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.

 

[Guest]

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

Hi Julie,

See David McRitchie's TrimAll sub at:

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


---
Regards,
Norman

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