Removing characters from the front of a text string

[Brian Feth]

Windows 11, Excel 2021

Can anyone tell me how to remove all of the 29 characters preceding the word VENDOR from the example below using VBA (or any other word in that location, "VENDOR” is an example).
Identifying what is unique to the position in the string; there are 29 preceding characters, there are 4 preceding spaces , and VENDOR is 3 characters after a "/".

Using VBA I want to change String1 into string 2,

String1 = PURCHASE AUTHORIZED ON 01/18 VENDOR Mktp US*R01QK Amzn.com/bill WA

String2 = VENDOR Mktp US*R01QK Amzn.com/bill WA

Help is always appreciated!

 

[Anthony47]

Maybe:

String2 = Mid(String1, 30)

This will put in String3 from character 30th on

 

[awoohaw]

Maybe this:

String2 = Right(String1, Len(words) - (InStr(1, String1, "/", vbTextCompare) + 2))

I'm confused with the "4 spaces", so I'm doing math on all the preceding character spaces, update as you see fit:

String2 = Right(String1, Len(String1) - (29 + 4 + 3))

 

[Brian Feth]

Maybe:

String2 = Mid(String1, 30)

This will put in String3 from character 30th on

Thank you for your reply. What you suggest is a spread sheet formula. I'm trying to do this using VBA code.

 

[Anthony47]

Thank you for your reply. What you suggest is a spread sheet formula

No, that's a vba statement

This will put in String2 from character 30th of String1

 

[Brian Feth]

Maybe this:

String2 = Right(String1, Len(words) - (InStr(1, String1, "/", vbTextCompare) + 2))

I'm confused with the "4 spaces", so I'm doing math on all the preceding character spaces, update as you see fit:

String2 = Right(String1, Len(String1) - (29 + 4 + 3))

 

[Brian Feth]

String2 = Right(String1, Len(String1) - (29 + 4 + 3))

Returns the characters after the the VENDOR (less a couple of characters). I'm trying to remove everything before the word VENDOR. I'm not sure it this matters in your formula but the vendor may be any number of characters.

 

[Scott Huish]

I think this is what you want:

Sub test()
string1 = "PURCHASE AUTHORIZED ON 01/18 VENDOR Mktp US*R01QK Amzn.com/bill WA"
string2 = "VENDOR Mktp US*R01QK Amzn.com/bill WA"

MsgBox Mid(string1, InStr(1, string1, "VENDOR"))
MsgBox Mid(string2, InStr(1, string2, "VENDOR"))
End Sub

 

[Brian Feth]

No, that's a vba statement

This will put in String2 from character 30th of String1

Sorry, my ignorance is showing. That absolutely works. Thank you so much. I don't want to be greedy but, having derived the string with the VENDOR and everything after it, can you tell me how to get ride of everything after the VENDOR?

 

[Brian Feth]

I think this is what you want:

Sub test()
string1 = "PURCHASE AUTHORIZED ON 01/18 VENDOR Mktp US*R01QK Amzn.com/bill WA"
string2 = "VENDOR Mktp US*R01QK Amzn.com/bill WA"

MsgBox Mid(string1, InStr(1, string1, "VENDOR"))
MsgBox Mid(string2, InStr(1, string2, "VENDOR"))
End Sub

The first of line of code does what I want. Thank you. Would it be difficult to remove everything after the word Vendor?