Simply... why isnt this TRIM() trimming please?

[Grizlore]

Hi all,

I am having a stupid day... and can't see why this isn't working...

Simply... why isnt this TRIM() trimming please?

Private Sub CommandButton1_Click()

        Dim sapFARM As String
        
        
        sapFARM = Trim("http://maps.google.co.uk/maps?f=d&source=s_d&saddr=SW1A1AA&daddr=" & txtPostcode.Value)

MsgBox sapFARM ' just to check visually if trimmed
     
        ActiveWorkbook.FollowHyperlink Trim(sapFARM), NewWindow:=True

End Sub

 

[GTO]

Where is it leaving spaces?

 

[Darren Bartrup]

Is this trying to TRIM the space from the postcode?

You'll need to use something like REPLACE as TRIM will only remove extra spaces - if there's only 1 space it will leave it in.

 

[Richard Schollar]

VBA Trim will only trim leading and trailing spaces so it works slightly differently to the worksheet function version of TRIM.

What value are you actually passing to it (ie what value does txtPostCode contain) and what result do you want to achieve?

 

[Grizlore]

D'oh

Silly me.. told you I was having one of those days!

Thanks, ignore me


Thanks for your time guys !

edit - Yes... I wanted all the spaces gone... so I shall replace them. I was using TRIM in error !

 

[bobsan42]

my guess:
since the first part has nothing to trim you probably only need to trim the secon one
so try:
sapFARM = "http://maps.google.co.uk/maps?f=d&source=s_d&saddr=SW1A1AA&daddr=" & trim(txtPostcode.Value)

 

[Darren Bartrup]

VBA Trim will only trim leading and trailing spaces so it works slightly differently to the worksheet function version of TRIM.

I stand corrected.

More importantly.... why do you need directions from Buckingham Palace?

 

[p45cal]

The Trim worksheet function works differently from the Trim in vba; They both remove all leading and trailing spaces, but the worksheet one additionally reduces to one space, any multiple spaces between words in a string.
To remove spaces altogether, use Substitute on a worksheet, or replace in vba:
x=substitute(A1," ","")
x=replace(oldstring," ","")

edit: goodness! the time to take a slurp of coffee while answering and an avalanche of answers..

 

[Grizlore]

I stand corrected.

More importantly.... why do you need directions from Buckingham Palace?

I don't, I swapped that in that postcode during testing

 

[Grizlore]

@p45cal

Thanks, I used your REPLACE suggestion

trimmedPCode = Replace(txtPostcode.Value, " ", "")

Thansk to everyone else who kindly gave suggestions