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...:) :)
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
Are you doing this in a worksheet formula? If so, the equivalent to xld's VBA is:
=TRIM(SUBSTITUTE(A1,CHAR(160),""))
PS You can check the character using:
=CODE(TRIM(A1))
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.