=TRIM() within a macro?

brasqo

New Member
Joined
Jul 10, 2019
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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)

VBA Code:
=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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
How about
VBA Code:
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.
 
Upvote 0
How about
VBA Code:
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!
 
Upvote 0
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’)
 
Upvote 0
Thanks for that.
The code should work for your version, but try
VBA Code:
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
 
Upvote 0
Solution
Thanks for that.
The code should work for your version, but try
VBA Code:
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!
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,874
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top