Replacing one character in variable position in a text string

[toontopo]

Hello,

I'm working with a large set of alphanumeric strings like these:

SO2TTO3SKA1LA3
SO2VRA3PPJU7`
SPA2KKA3PJE7TRE3
SPO1RT
SPU3NTA1RE3

They contain single-digit numbers as well as letters. I need to replace the third number from the RIGHT with "9" if:
1) the third number from the right is a 3, the second number from the right a 1, and the first number from the right again a 3 AND
2) there are at least three letters between the third and the second number from the right except if the letter immediately following the third number from the right is an S.

Thus, in the set above, the fifth string would change to SPU9NTA1RE3. All other ones would remain unchanged (the first one, SO2TTO3SKA1LA3, has the pattern 3-1-3 and three letters between 3 and 1 but the first of those is an S).

Any help would be greatly appreciated!

Many thanks

[Andrew-R]

The only reasonable way I can see of doing this is with a user-defined function (UDF):

Please Login or Register  to view this content.

Putting this code into a module in your workbook will let you use StrangeReplace as a function, so if your string was in A1 in B1 you could have:

=STRANGEREPLACE(A1)

Does that help?

[Ron Coderre]

With
Col_A containing text strings
SO2TTO3SKA1LA3
SO2VRA3PPJU7
SPA2KKA3PJE7TRE3
SPO1RT
SPU3NTA1RE3
SPU3NTA1REZZZ3

If a regular formula is acceptable...
try this

Please Login or Register  to view this content.

Copy that formula down as far as you need

In the above example, the formulas return:
SO2TTO3SKA1LA3
SO2VRA3PPJU7
SPA2KKA3PJE7TRE3
SPO1RT
SPU9NTA1RE3 <----Changed
SPU9NTA1REZZZ3 <----Changed


Is that something you can work with?

[Andrew-R]

Damn you, Ron, I was busy working out my own formula solution.

Admittedly mine wasn't *quite* as compact as yours:

=IF(SUMPRODUCT(--(ISNUMBER(VALUE(MID(A5,ROW(INDIRECT("1:" & LEN(A5))),1)))))<3,A5,IF(MID(A5,LEN(A5)-FIND("1",TEXT(SUMPRODUCT(--(ISNUMBER(VALUE(MID(A5,ROW(INDIRECT("1:" & LEN(A5))),1))))*10^ROW(INDIRECT("1:" & LEN(A5)))),REPT("0",LEN(A5))))+1,1)<>"3",A5,IF(MID(A5,LEN(A5)-FIND("~",SUBSTITUTE(TEXT(SUMPRODUCT(--(ISNUMBER(VALUE(MID(A5,ROW(INDIRECT("1:" & LEN(A5))),1))))*10^ROW(INDIRECT("1:" & LEN(A5)))),REPT("0",LEN(A5))),"1","~",2))+1,1)<>"1",A5,IF(MID(A5,LEN(A5)-FIND("~",SUBSTITUTE(TEXT(SUMPRODUCT(--(ISNUMBER(VALUE(MID(A5,ROW(INDIRECT("1:" & LEN(A5))),1))))*10^ROW(INDIRECT("1:" & LEN(A5)))),REPT("0",LEN(A5))),"1","~",3))+1,1)<>"3",A5,IF(MID(A5,LEN(A5)-FIND("~",SUBSTITUTE(TEXT(SUMPRODUCT(--(ISNUMBER(VALUE(MID(A5,ROW(INDIRECT("1:" & LEN(A5))),1))))*10^ROW(INDIRECT("1:" & LEN(A5)))),REPT("0",LEN(A5))),"1","~",3))+2,1)="S",A5,IF(FIND("~",SUBSTITUTE(TEXT(SUMPRODUCT(--(ISNUMBER(VALUE(MID(A5,ROW(INDIRECT("1:" & LEN(A5))),1))))*10^ROW(INDIRECT("1:" & LEN(A5)))),REPT("0",LEN(A5))),"1","~",3))-FIND("~",SUBSTITUTE(TEXT(SUMPRODUCT(--(ISNUMBER(VALUE(MID(A5,ROW(INDIRECT("1:" & LEN(A5))),1))))*10^ROW(INDIRECT("1:" & LEN(A5)))),REPT("0",LEN(A5))),"1","~",2))<3,A5,LEFT(A5, LEN(A5)-FIND("~",SUBSTITUTE(TEXT(SUMPRODUCT(--(ISNUMBER(VALUE(MID(A5,ROW(INDIRECT("1:" & LEN(A5))),1))))*10^ROW(INDIRECT("1:" & LEN(A5)))),REPT("0",LEN(A5))),"1","~",3))) & "9" & MID(A5,LEN(A5)-FIND("~",SUBSTITUTE(TEXT(SUMPRODUCT(--(ISNUMBER(VALUE(MID(A5,ROW(INDIRECT("1:" & LEN(A5))),1))))*10^ROW(INDIRECT("1:" & LEN(A5)))),REPT("0",LEN(A5))),"1","~",3))+2,255)))))))

Truly you are the god of formula.

[toontopo]

Sorry, haven't looked at the other posts. Will be back soon...

[Ron Coderre]

Damn you, Ron, I was busy working out my own formula solution.

Admittedly mine wasn't *quite* as compact as yours:

=IF(SUMPRODUCT(--(ISNUMBER(VALUE(MID(A5,ROW(INDIRECT("1:" & LEN(A5))),1)))))<3,A5,IF(MID(A5,LEN(A5)-FIND("1",TEXT(SUMPRODUCT(--(ISNUMBER(VALUE(MID(A5,ROW(INDIRECT("1:" & LEN(A5))),1))))*10^ROW(INDIRECT("1:" & LEN(A5)))),REPT("0",LEN(A5))))+1,1)<>"3",A5,IF(MID(A5,LEN(A5)-FIND("~",SUBSTITUTE(TEXT(SUMPRODUCT(--(ISNUMBER(VALUE(MID(A5,ROW(INDIRECT("1:" & LEN(A5))),1))))*10^ROW(INDIRECT("1:" & LEN(A5)))),REPT("0",LEN(A5))),"1","~",2))+1,1)<>"1",A5,IF(MID(A5,LEN(A5)-FIND("~",SUBSTITUTE(TEXT(SUMPRODUCT(--(ISNUMBER(VALUE(MID(A5,ROW(INDIRECT("1:" & LEN(A5))),1))))*10^ROW(INDIRECT("1:" & LEN(A5)))),REPT("0",LEN(A5))),"1","~",3))+1,1)<>"3",A5,IF(MID(A5,LEN(A5)-FIND("~",SUBSTITUTE(TEXT(SUMPRODUCT(--(ISNUMBER(VALUE(MID(A5,ROW(INDIRECT("1:" & LEN(A5))),1))))*10^ROW(INDIRECT("1:" & LEN(A5)))),REPT("0",LEN(A5))),"1","~",3))+2,1)="S",A5,IF(FIND("~",SUBSTITUTE(TEXT(SUMPRODUCT(--(ISNUMBER(VALUE(MID(A5,ROW(INDIRECT("1:" & LEN(A5))),1))))*10^ROW(INDIRECT("1:" & LEN(A5)))),REPT("0",LEN(A5))),"1","~",3))-FIND("~",SUBSTITUTE(TEXT(SUMPRODUCT(--(ISNUMBER(VALUE(MID(A5,ROW(INDIRECT("1:" & LEN(A5))),1))))*10^ROW(INDIRECT("1:" & LEN(A5)))),REPT("0",LEN(A5))),"1","~",2))<3,A5,LEFT(A5, LEN(A5)-FIND("~",SUBSTITUTE(TEXT(SUMPRODUCT(--(ISNUMBER(VALUE(MID(A5,ROW(INDIRECT("1:" & LEN(A5))),1))))*10^ROW(INDIRECT("1:" & LEN(A5)))),REPT("0",LEN(A5))),"1","~",3))) & "9" & MID(A5,LEN(A5)-FIND("~",SUBSTITUTE(TEXT(SUMPRODUCT(--(ISNUMBER(VALUE(MID(A5,ROW(INDIRECT("1:" & LEN(A5))),1))))*10^ROW(INDIRECT("1:" & LEN(A5)))),REPT("0",LEN(A5))),"1","~",3))+2,255)))))))

Truly you are the god of formula.

Yikes! My computer monitor sagged trying to hold up that formula!
(Thanks for the kind words)

---------- Post added at 10:24 AM ---------- Previous post was at 10:17 AM ----------

Hi,
many thanks for taking to the trouble of thinking of a solution to what must look totally weird. When putting your code into my workbook, I get the the NAME? error though. Could I have overlooked something very basic?

That error indicates that either
• a function name is entered incorrectly
...fix by editing the function name

or...perhaps because you have MAC
• you don't have the IFERROR function.
...fix by using this variation of the formula I posted

Please Login or Register  to view this content.

Does that help?

[Ron Coderre]

Ummmm...How did my reply to toontopo end up getting appended to Andrew-R's post?

---------- Post added at 10:38 AM ---------- Previous post was at 10:37 AM ----------

Sorry, haven't looked at the other posts. Will be back soon...

Somehow my reply to you got appended to my reply to Andrew-R's post. I have no idea how that happened.

[Andrew-R]

I think my formula broke the forum

[Ron Coderre]

Sorry, haven't looked at the other posts. Will be back soon...

One more try...my posts are getting appended to my replies to Andrew-r. I don't know why.

---------- Post added at 10:45 AM ---------- Previous post was at 10:45 AM ----------

I think my formula broke the forum

THAT's funny!

[jindon]

UDF

=Replace3(A1)

Please Login or Register  to view this content.

[toontopo]

Hi Ron, many thanks for this, I'm in awe! Indeed I have a Mac. Unfortunately though, I still get the error message "The formula you typed contained an error" even with your new formula. Thank you again.

[Ron Coderre]

Hi Ron, many thanks for this, I'm in awe! Indeed I have a Mac. Unfortunately though, I still get the error message "The formula you typed contained an error" even with your new formula. Thank you again.

Can you post a sample workbook with the formula I posted, so we can see what's going wrong?

[toontopo]

Here it is , without your formula though because the computer wouldn't let me save it with it.

[Ron Coderre]

Here it is , without your formula though because the computer wouldn't let me save it with it.

Here you go...see if the attached workbook helps.

[romperstomper]

Ron's original formula does work in Excel 2011. Sample attached.

[toontopo]

Brilliant, thank you so much guys! Also many thanks to jindon.

[Ron Coderre]

Ron's original formula does work in Excel 2011. Sample attached.

Thanks, R.
BTW...no need for the array formulas. The regular formula works just fine...(Unless: Is C+S+E required in Excel 2011?)

[romperstomper]

No - I just saw the MID(...ROW(INDIRECT(..)) construct and my fingers automatically did the CSE.

[daddylonglegs]

Am I reading the requirement wrong? If the cell value is TO3BO1GA3 should that change?, surely there are only 2 characters between the third and second numbers from the right?

[Ron Coderre]

Am I reading the requirement wrong? If the cell value is TO3BO1GA3 should that change?, surely there are only 2 characters between the third and second numbers from the right?

DL...Nice catch! Thanks. You're absolutely right.
The formula I posted has no test for 3-or-more-letters between the 3rd and 2nd digits from the right.
This regular formula corrects that oversight:

Please Login or Register  to view this content.