Need trim to include leading space

[Sir Babydum GBE]

The title does what it says on the tin...

How do I get TRIM() formula to also take out leading space? Sometimes there's more than one leading space - but trim seems to take care of all bar the leading one.

Any ideas please?

BD

[Bob Phillips]

Are you sure about that BD?

[vba]

Dim sTest As String

sTest = " hello"
Debug.Print Len(sTest) & " - " & Len(Trim(sTest))
sTest = " hello"
Debug.Print Len(sTest) & " - " & Len(Trim(sTest))
[/vba]

[tstav]

BD, what is 'tin' and what is 'bar'? Pease reaf dwice befose porting...

[rory]

If your TRIM formula is leaving a space, it's probably not a "normal" space, but a CHAR(160), especially if the data came from the web.

[Sir Babydum GBE]

tstav - "Does what it says on the tin" here means that the post's subject is the same as the question. "Bar" means "except". apologies.

XLD - I'm not sure what your reply means. But when you ask "am i sure about that"... If I type a few leading spaces into a text input cell - then trim removes all the spaces. But it doesn't seem to work on the data that's been imported. That's where I need it to work.

I've just seen Rory's reply (cheers Rory) - so given that these spaces may be CHAR(160) thingimmyjigs (sorry tstav) can they be gotten rid of by a formula without taking out genuine spaces between words?

[Bob Phillips]

It means I am showing you that TRIM works on spaces.

Try this

[vba]

Dim sTest As String

sTest = Chr(160) & "hello"
sTest = " " & sTest
Debug.Print Len(sTest) & " - " & Len(Trim(Replace(sTest, Chr(160), "")))
[/vba]

or get Dave McRitchie's all-encompassing TRIMALL function http://www.mvps.org/dmcritchie/excel/join.htm#trimall

[rory]

Are you doing this in a worksheet formula? If so, the equivalent to xld's VBA is:
=TRIM(SUBSTITUTE(A1,CHAR(160),""))

[rory]

PS You can check the character using:
=CODE(TRIM(A1))