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

Hi Julie,

See David McRitchie's TrimAll sub at:

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


---
Regards,
Norman



"Julie Beth" <Julie [email protected]> wrote in message
news:[email protected]...
>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

how about this

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


--


Gary


"Julie Beth" <Julie [email protected]> wrote in message
news:[email protected]...
>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

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

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 [email protected]> wrote in message
> news:[email protected]...
> >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
>
>
>