Link to home
Start Free TrialLog in
Avatar of New_Alex
New_AlexFlag for Cyprus

asked on

VBA, Remove last portion of a string that matches the format "*nn/12"

Here is the String:

Example1: MyString = "VariableContent*nn/12" (nn must me any number)
Example2: Mystring = "Rapids 12 month Ratio: R*9/  12"
Example3: MyString = "Cats food yearly *8 *4 /12"
Example4: MyString = "*Yearly Ratio * 11/12    "


I just want to remove the last portion of it but only if it is of the format "*n/12" where n can be any number from 0. * is the plain asterix symbol "*"

Any Ideas?

Thanks in advance...
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

You want to remove everything after the * ?
=MID(A1,1,FIND("*",A1,1)-1) if all your examples in Col A
if it is not that pls clarify by what you expect the result to be
gowflow
SOLUTION
Avatar of kaufmed
kaufmed
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I was just about to post the regexp solution as well, but kaufmed has got there first - don't forget that to make it work you'll need to add a reference to Microsoft VBScript Regular Expressions 5.5
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Bill Prew
Bill Prew

Whoops, gotta refrsh more often...

~bp
Avatar of New_Alex

ASKER

I adopted plummet solution even though it had many flows,  just to avoid RegEx.
So I guess you ended up with something like this?

Function Extract(s)
  i = InStrRev(s, "*")
  If i > 0 Then Extract = Trim(Mid(s, i + 1))
End Function

Open in new window

~bp