trim leading spaces

Hiya,

I have a flat database in excel with the problem that some text fields
have leading spaces, which mucks the sort. I don't have the access
database.

It's not clear to me how trim can help with this problem.


from the help page:

=TRIM(A2) Removes the trailing space from the string "BD 122 " (BD 112)


However, I want the *leading* spaces, not the trailing spaces. I don't
want a VB script or macro, but are those the only options?

I can group the offending rows, export them to notepad, and re-import.
Is there something import can do to simply ignore leading spaces for
imports? Not all spaces, just leading.




thanks,


Thufir

Trim remove leading, trailing and extra spaces


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



<[email protected]> wrote in message
news:[email protected]...
> Hiya,
>
> I have a flat database in excel with the problem that some text fields
> have leading spaces, which mucks the sort. I don't have the access
> database.
>
> It's not clear to me how trim can help with this problem.
>
>
> from the help page:
>
> =TRIM(A2) Removes the trailing space from the string "BD 122 " (BD 112)
>
>
> However, I want the *leading* spaces, not the trailing spaces. I don't
> want a VB script or macro, but are those the only options?
>
> I can group the offending rows, export them to notepad, and re-import.
> Is there something import can do to simply ignore leading spaces for
> imports? Not all spaces, just leading.
>
>
>
>
> thanks,
>
>
> Thufir
>

=trim() remove any duplicated embedded spaces, too.

So (with * representing a space)
**BD**122*
=trim() would return
BD*122

If you don't have any of those embedded spaces, you could use this formula to
keep the leading (but not duplicate embedded) spaces:

=REPT(" ",SEARCH(LEFT(TRIM(A1),1),A1)-1)&TRIM(A1)

**BD*122****
would become
**BD*122

But
**BD****12****
would become
**BD*12



[email protected] wrote:
>
> Hiya,
>
> I have a flat database in excel with the problem that some text fields
> have leading spaces, which mucks the sort. I don't have the access
> database.
>
> It's not clear to me how trim can help with this problem.
>
> from the help page:
>
> =TRIM(A2) Removes the trailing space from the string "BD 122 " (BD 112)
>
> However, I want the *leading* spaces, not the trailing spaces. I don't
> want a VB script or macro, but are those the only options?
>
> I can group the offending rows, export them to notepad, and re-import.
> Is there something import can do to simply ignore leading spaces for
> imports? Not all spaces, just leading.
>
> thanks,
>
> Thufir

--

Dave Peterson

As the others have said the TRIM() worksheet function will remove the pesky
leading spaces, as well as trailing and duplicate spaces.

If you wanted to remove the leading spaces, but keep the trailing and
duplicate spaces, the VBA function LTrim() will do this. But you said you did
not want a macro..? Just make your own worksheet function then.

How? Go to: Tools -> macro -> visual basic editor

Find your excel file in the project explorer on the left, right click it and
select insert -> module. A new module (Module1, most likely) will be inserted
under the modules sub folder in the project explorer, double click it and
paste the following into the code window that comes up:

Function LeadTrim(str As String) As String
LeadTrim = LTrim(str)
End Function

Now you can type =LeadTrim(A1) into any cell in your workbook and it will
trim only the leading spaces off the cell you reference.

"[email protected]" wrote:

> Hiya,
>
> I have a flat database in excel with the problem that some text fields
> have leading spaces, which mucks the sort. I don't have the access
> database.
>
> It's not clear to me how trim can help with this problem.
>
>
> from the help page:
>
> =TRIM(A2) Removes the trailing space from the string "BD 122 " (BD 112)
>
>
> However, I want the *leading* spaces, not the trailing spaces. I don't
> want a VB script or macro, but are those the only options?
>
> I can group the offending rows, export them to notepad, and re-import.
> Is there something import can do to simply ignore leading spaces for
> imports? Not all spaces, just leading.
>
>
>
>
> thanks,
>
>
> Thufir
>
>

[email protected] wrote...
>I have a flat database in excel with the problem that some text fields
>have leading spaces, which mucks the sort. I don't have the access
>database.
....

Here it's the leading spaces you claim that screw up the sort.

>However, I want the *leading* spaces, not the trailing spaces. I don't
>want a VB script or macro, but are those the only options?
....

Yet here you say you want the leading spaces.

Confusing.

As a theoretical matter, you could delete leading spaces using

=REPLACE(s,1,FIND(LEFT(TRIM(s),1),s)-1,"")

and trailing spaces using the more complicated array formula

=LEFT(s,MATCH(2,1/(MID(s,ROW(INDEX($1:$65536,1,1):
INDEX($1:$65536,LEN(s),1)),1)<>" ")))

Both formulas preserve runs of interior spaces.

Harlan Grove wrote:
> [email protected] wrote...
> >I have a flat database in excel with the problem that some text fields
> >have leading spaces, which mucks the sort. I don't have the access
> >database.
> ...
>
> Here it's the leading spaces you claim that screw up the sort.
>
> >However, I want the *leading* spaces, not the trailing spaces. I don't
> >want a VB script or macro, but are those the only options?
> ...
>
> Yet here you say you want the leading spaces.
>
> Confusing.
....

Typo!

Please read that as:

However, I want to trim the *leading* spaces, not the trailing spaces.



Thanks,

Thufir

KLEBESTIFT wrote:
....
> If you wanted to remove the leading spaces, but keep the trailing and
> duplicate spaces, the VBA function LTrim() will do this. But you said you did
> not want a macro..? Just make your own worksheet function then.
>
> How? Go to: Tools -> macro -> visual basic editor
>
> Find your excel file in the project explorer on the left, right click it and
> select insert -> module. A new module (Module1, most likely) will be inserted
> under the modules sub folder in the project explorer, double click it and
> paste the following into the code window that comes up:
>
> Function LeadTrim(str As String) As String
> LeadTrim = LTrim(str)
> End Function
>
> Now you can type =LeadTrim(A1) into any cell in your workbook and it will
> trim only the leading spaces off the cell you reference.
....

I'm mystified at the concept of typing that into *any*&

I was contemplating exporting the offending column to notepad and doing
it manually, but I expect that the above solution will work on most any
windows XP computer with Excel installed? I don't have admin access,
but don't mind a small script. I just didn't want to install VB,
compile, or do anything of that nature. The above solution looks
easily doable


thanks all, very much,


Thufir

[email protected] wrote...
>Harlan Grove wrote:
....
>> Confusing.
>...
>
>Typo!
>
>Please read that as:
>
>However, I want to trim the *leading* spaces, not the trailing spaces.

So you didn't try the first formula I provided?

Harlan Grove wrote:
> [email protected] wrote...
> >Harlan Grove wrote:
> ...
> >> Confusing.
> >...
> >
> >Typo!
> >
> >Please read that as:
> >
> >However, I want to trim the *leading* spaces, not the trailing spaces.
>
> So you didn't try the first formula I provided?

=REPLACE(s,1,FIND(LEFT(TRIM(s),1),s)-1,"")

Is, on reflection, probably the solution I'm looking for.

To use excel, I must borrow a computer with Excel, so I haven't had as
much time as I would've liked to try this.

The VB solution works, however it doesn't "stick" in that when I
re-open the spreadsheet I get errors. Also, it leaves me with the
original column which I'd like to discard.

The offending column is A. Where and how do I use this formula,
please?



Thanks,

Thufir

[email protected] wrote...
....
>=REPLACE(s,1,FIND(LEFT(TRIM(s),1),s)-1,"")
>
>Is, on reflection, probably the solution I'm looking for.
>
>To use excel, I must borrow a computer with Excel, so I haven't had as
>much time as I would've liked to try this.
>
>The VB solution works, however it doesn't "stick" in that when I
>re-open the spreadsheet I get errors. Also, it leaves me with the
>original column which I'd like to discard.
>
>The offending column is A. Where and how do I use this formula,
>please?

The formula above would involve an extra column. If your original data
were in column A beginning in cell A3 (for example), you could put the
following formula in cell B3,

=REPLACE(A3,1,FIND(LEFT(TRIM(A3),1),A3)-1,"")

and it should evaluate to what's in A3 with the leading spaces removed.
If your data in column A extended down to cell A200, copy B3 and paste
into B4:B200. Then select B3:B200, run the menu command Edit > Copy,
move to cell A3, run the menu command Edit > Paste Special, select
Values from the Paste Special dialog and click OK. Then clear B3:B200.

Harlan Grove wrote:
....
> =REPLACE(A3,1,FIND(LEFT(TRIM(A3),1),A3)-1,"")
....

Worked nicely, thanks



-Thufir