trim leading spaces

[hawat.thufir]

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

 

[Peo Sjoblom]

Trim remove leading, trailing and extra spaces


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[Dave Peterson]

=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

 

[KLEBESTIFT]

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.

 

[Harlan Grove]

(e-mail address removed) 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.

 

[hawat.thufir]

(e-mail address removed) wrote...
...

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

...

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

 

[hawat.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

 

[Harlan Grove]

(e-mail address removed) wrote...

Harlan Grove wrote: ....
...

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?

 

[hawat.thufir]

(e-mail address removed) wrote...

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

 

[Harlan Grove]

(e-mail address removed) 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.

 

[hawat.thufir]

Harlan Grove wrote:
....

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

....

Worked nicely, thanks



-Thufir