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!

 

[awoohaw]

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?

Are you saying you only want to get the VENDOR (or whatever text it is your looking for) from the VBA procedure, and only that?
Can the word you want have spaces in it?

 

[Brian Feth]

Are you saying you only want to get the VENDOR (or whatever text it is your looking for) from the VBA procedure, and only that?
Can the word you want have spaces in it?

Yes, I want to pull the word VENDOR out of the statement. I can probably get by without considering whether the VENDER has a space in it. There would be an issue with businesses named THE VENDER but it need be I can deal with those by hand. It would be great to have code that would deal with a space or , second best would be a code that recognized the word
a code that recognized the word THE and dealt with it would probably deal with 95% of the issues. Thank you for your help.

 

[awoohaw]

Yes, I want to pull the word VENDOR out of the statement. I can probably get by without considering whether the VENDER has a space in it. There would be an issue with businesses named THE VENDER but it need be I can deal with those by hand. It would be great to have code that would deal with a space or , second best would be a code that recognized the word
a code that recognized the word THE and dealt with it would probably deal with 95% of the issues. Thank you for your help.

Having a space causes a problem. Is there a unique consistent character after the full vendor name?

 

[Brian Feth]

Having a space causes a problem. Is there a unique consistent character after the full vendor name?

These are the lines describing credit card purchases on a bank statement. The Vendors are the names of retailers. There are no consistent special characters. For now ignoring the problem of spaces is just fine. I can make a few corrects as I go. Over time I may see some other way to solve the issue. Most of the time a business with a two (or more) words in the name will be recognizable by one word. The only consistent problem will be business with THE in the name. The print out will show the business as THE, and it will be easy to identify and make corrections. Thanks again for you help.

 

[awoohaw]

These are the lines describing credit card purchases on a bank statement. The Vendors are the names of retailers. There are no consistent special characters. For now ignoring the problem of spaces is just fine. I can make a few corrects as I go. Over time I may see some other way to solve the issue. Most of the time a business with a two (or more) words in the name will be recognizable by one word. The only consistent problem will be business with THE in the name. The print out will show the business as THE, and it will be easy to identify and make corrections. Thanks again for you help.

You could perform a replace function before the InStr function on String1,

String1 = Replace(String1, "THE ", "", 1, , vbTextCompare)

If there are no "THE " you will not get an error.

string1 = Replace(string1, "THE ", "", 1, , vbTextCompare)
STRING2 = Mid(string1, 30)
STRING2 = Left(STRING2, InStr(1, STRING2, " ", vbTextCompare))

 

[Brian Feth]

You could perform a replace function before the InStr function on String1,

String1 = Replace(String1, "THE ", "", 1, , vbTextCompare)

If there are no "THE " you will not get an error.

string1 = Replace(string1, "THE ", "", 1, , vbTextCompare)
STRING2 = Mid(string1, 30)
STRING2 = Left(STRING2, InStr(1, STRING2, " ", vbTextCompare))

Works perfectly. I'm impressed. That you very much!

 

[awoohaw]

Happy to help. Best Wishes.