vba to remove string upto specific word

Question

I have a string with value "NA - Phase 1: Preliminary Phase" and I want to remove all the words before the dash "-".

Here is my current code, I am able to remove the string, but it keeps the "-", which I also want to remove.

indexOfThey = InStr(1, s, "-")
finalString = VBA.Mid(s, indexOfThey)
wksSourced.Cells(lastrowd + 1, 9).Value = finalString

If you know the dash is always followed by a space, you can just use Mid(s, indexOfThey + 2) — Wolfie
– Wolfie, Commented May 1, 2017 at 9:35
why don't use excel functions in VBA WorksheetFunction Library. — hackwithharsha
– hackwithharsha, Commented May 1, 2017 at 13:35
=RIGHT(G14,LEN(G14)-FIND("-",G14)) ' G14 is the cell referencing to the data — hackwithharsha
– hackwithharsha, Commented May 1, 2017 at 13:35

Slai · Accepted Answer · 2017-05-01 10:22:05Z

1

You can just replace it directly:

wksSourced.Cells(lastrowd + 1, 9).Replace "*- ", "", xlPart

edited May 1, 2017 at 10:22

answered May 1, 2017 at 9:36

Slai

23k5 gold badges49 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gaus Shaikh Over a year ago

but I want to remove all the words Eg: "NA - " and not only "-"

Variatus · Accepted Answer · 2017-05-01 10:08:22Z

0

I would want to make the modification of the cell contents conditional on there being a dash in it in the first place. I should also like to trim any blank spaces that may remain after the removal. Therefore I would modify your original code like this:-

n = InStr(S, "-")
If n Then wksSourced.Cells(lastrowd + 1, 9).Value = Trim(Mid(S, n + 1))

However, if the string S is already resident in the specified cell the same idea would be expressed like this:-

With wksSourced.Cells(lastrowd + 1, 9)
    n = InStr(.Value, "-")
    If n Then .Value = Trim(Mid(.Value, n + 1))
End With

answered May 1, 2017 at 10:08

Variatus

14.4k2 gold badges16 silver badges35 bronze badges

Collectives™ on Stack Overflow

vba to remove string upto specific word

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related