Replacing part of a string (Excel VBA)

Question

I have the following data in Sheet 1:

                 A
1   1C BAC Z6 : CHF QM : 1M TOIS
2   2C BAC Z6 : CHF QM : 1M TOIS
3   3C BAC H7 : CHF QM : 1M TOIS
4   30C BAC H7 : CHF AM : 1M TOIS
5   45C BAC M7 : CHF QM : 1M TOIS
6   5C BAC Z7 : CHF QM : 1M TOIS

I'm trying to replace the 2-letter code that follows "BAC", but can't seem to do so using the REPLACE() function. I'm not familiar enough with VBA to use InStr either.

Background: the 2-character code that follows "BAC" represents a month and a year.

The first character in the 2-character code represents a month. For example,

"H" represents March

"M" represents June

"U" represents September

"Z" represents December

There are only four months (March, June, September, December).

The second character in the 2-character code represents the year. For example,

"6" represents 2016

"7" represents 2017

"8" represents 2018 etc, etc

Does anyone know of a solution that can replace the 2-character code with next 2-character code (i.e. the next month and year)?

For example,

"Z6" (December 2016) would become "H7" (March 2017)

"H7" (March 2017) would become "M7" (June 2017)

"M7" (June 2017) would become "U7" (September 2017)

"U7" (September 2017) would become "Z7" (December 2017)

"Z7" (December 2017) would become "H8" (March 2018)

etc, etc.

Thanks in advance for your help with this!

Just to clarify: A1 contains 1C BAC Z6 : CHF QM : 1M TOIS? — Wiktor Stribiżew

Ron Rosenfeld Ron Rosenfeld · Accepted Answer · 2016-09-27T15:23:58

Try:

=REPLACE(A1,FIND("BAC",A1)+4,2,VLOOKUP(MID(A1,FIND("BAC",A1)+4,1),{"H","M";"M","U";"U","Z";"Z","H"},2,FALSE)&MID(A1,FIND("BAC",A1)+5,1)+(MID(A1,FIND("BAC",A1)+4,1)="Z"))

We use the REPLACE function along with a VLOOKUP to determine what to replace the letter part of the code.

FIND locates the two character code to be replaced by REPLACE or examined with the MID function.

VLOOKUP decides to replace H with M, M with U, etc

The Boolean equality test at the end decides to increment the number by one if and only if the letter is Z.

EDIT: The OP indicated he might want to search for one of several codes preceding the part to be replaced. This can be done by using an array constant in the FIND function find_text parameter. We also add these constants on to the end of the string in order to avoid errors depending on which is not found. MIN returns the smallest number which, if the key is found, will be within the key.

In this modification, I also used the MOD function so that 9 would roll over to 0.

=REPLACE(A1,MIN(FIND({"BAC","EMC"},A1&"BACEMC"))+4,2,VLOOKUP(MID(A1,MIN(FIND({"BAC","EMC"},A1&"BACEMC"))+4,1),{"H","M";"M","U";"U","Z";"Z","H"},2,FALSE)&MOD(MID(A1,MIN(FIND({"BAC","EMC"},A1&"BACEMC"))+5,1)+(MID(A1,MIN(FIND({"BAC","EMC"},A1&"BACEMC"))+4,1)="Z"),10))

Replacing part of a string (Excel VBA)

1 Answers