PDA

View Full Version : Need trim to include leading space



Sir Babydum GBE
04-24-2008, 03:59 AM
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
04-24-2008, 04:35 AM
Are you sure about that BD?



Dim sTest As String

sTest = " hello"
Debug.Print Len(sTest) & " - " & Len(Trim(sTest))
sTest = " hello"
Debug.Print Len(sTest) & " - " & Len(Trim(sTest))

tstav
04-24-2008, 04:42 AM
BD, what is 'tin' and what is 'bar'? Pease reaf dwice befose porting...:) :)

rory
04-24-2008, 04:55 AM
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
04-24-2008, 05:00 AM
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
04-24-2008, 05:06 AM
It means I am showing you that TRIM works on spaces.

Try this



Dim sTest As String

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


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

rory
04-24-2008, 05:12 AM
Are you doing this in a worksheet formula? If so, the equivalent to xld's VBA is:
=TRIM(SUBSTITUTE(A1,CHAR(160),""))

rory
04-24-2008, 05:13 AM
PS You can check the character using:
=CODE(TRIM(A1))