Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Trim any word

0
        remo = Sheets("ArtList").Range("G" & ActiveCell.Row).Value
        str = Trim(Replace(remo, "TV-," & "", "", 6, 1))

full TV-5245-6525

Cut only TV-

and keeps 45-6525

My question is how can i use this function always even if not starts with TV

it will always ends with -

because i know if we using SPIT- 

so the result will be 5245-6525 :( but i need 45-6525 instead

Any solutions please.

Thanks

excel
MS Office Version: 2017
posted by: GhostofWanted (rep: 46) on Sun Apr 25, 2021 at 2:40 am
Answer
Discuss

Answers

0
Selected Answer

Ghost

If you want to discard the alphabetic start (TV-, SPIT- or whatever) plus the first two digits, use something like this to find where the first "-" appears then subtract that character position from the length of the string (plus the 2 digits you want to remove too):

Str1 = Trim(Right(remo, Len(remo) - InStr(remo, "-") - 2))
Note that I changed the variable name to Str1 since it's not good practice to name your variable the same as VBA functions (remember VBA already has a Str function to return a string from a number). VBA does let you declare a variable with the same name as a function but it can confuse others (like me!).

Hope this works for you.

posted by: John_Ru (rep: 6142) on Sun Apr 25, 2021 at 4:07 am
Discuss

Discussion

Hello John_Ru

Awseome works like i wanted it :D
Thanks John_Ru

Also i changed the Str to Str1 didn't know that VBA  has that function name.
So learned again something new today ;)
GhostofWanted (rep: 46) Apr 25, '21 at 4:25 am
Great! Thanks for selecting my answer Ghost
John_Ru (rep: 6142) Apr 25, '21 at 4:26 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login