=TRIM() within a macro?

[brasqo]

Hello,

Hopefully I can explain this without sounding like a dope =/

Quick background, I use the following TRIM() formula on a worksheet column on values that are all similar (IE: ABCDEFG Z307G- Z2CGK / Z307G.... These values could have different lengths, and the below formula seems to work seamlessly thus-far)

=TRIM(RIGHT(B2,FIND(" ",B2)-2))

This subsequently Trims out the fat, and leaves the value I require (Z307G in the above example)...

I was hoping to create a macro that would perform this same task by Trimming and printing the new shorter value in the same column (In this example, Column B) as what was being trimmed (replacing the original longer string of data).

I'm not understanding how I'd be able to implement the above =TRIM() into a VBA macro...
I've looked at VBA.Trim and WorksheetFunction.Trim, but just cant wrap my head around how I'd be able to implement the complete formula that I know works.

If anyone can point me in the right direction it would be appreciated greatly.

Thank you all in advance

 

[Fluff]

How about

Sub brasqo()
   With Range("B2", Range("B" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("trim(right(@,find("" "",@)-2))", "@", .Address))
   End With
End Sub

BUT try this in a copy of your workbook.

 

[brasqo]

How about

Sub brasqo()
   With Range("B2", Range("B" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("trim(right(@,find("" "",@)-2))", "@", .Address))
   End With
End Sub

BUT try this in a copy of your workbook.

Thank you Fluff for taking a look! -
This seems to do the actual trimming correctly, however it applies the new B2 value to each cell below it (in the B column) rather than trimming each individual cell in Column B as needed (they have different names but are the same format)...

I'd assume I would need to adjust the Range in your first line? I tried changing your "With Range("B2"..." to "With Range("B2:B500")..." but it produced the same result...

I'm probably completely wrong in my thinking =/

Thank you for your time!

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[brasqo]

Did it, thank you again Fluff

 

[Fluff]

Thanks for that.
The code should work for your version, but try

Sub brasqo()
   With Range("B2", Range("B" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if({1},trim(right(@,find("" "",@)-2)))", "@", .Address))
   End With
End Sub

 

[brasqo]

Thanks for that.
The code should work for your version, but try

Sub brasqo()
   With Range("B2", Range("B" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if({1},trim(right(@,find("" "",@)-2)))", "@", .Address))
   End With
End Sub

This worked brilliantly!
Thank you Fluff - I learned a bunch just looking at your code!

 

[Fluff]

You're welcome & thanks for the feedback.