How can I substitute characters in a string when the same characters are used in both input and output strings

Question

How can I substitute characters in a string when the same characters are used in both input and output strings

Hi Numbers users,

I am trying to get Numbers 2.3 on OS X (10.9.1) to convert cells containing variable length strings composed of 6 letters, A, B, C, a, b, c into a second string containing the same 6 lettersusing the rule A to c, B to b, and C to a and vice versa, so for example cell A1 might contain string "AcBbCaaB" and I want Numbers to convert this into "cAbBaCCb" in cell B1. I have tried to use the SUBSTITUTE function (nested) without success so far as I end up with the letters either all uppercase or all lowercase. I understand why this is happening and maybe it isn't possible using SUBSTITUTE. Does anyone know if there is another function I can use to do this?

Thanks, Hugh.

iMac (27-inch Late 2009), OS X Mountain Lion (10.8.4)

Posted on Dec 19, 2013 11:04 AM

Reply

Answer 1

Dec 19, 2013 12:33 PM in response to hughmacfarlane

you can use this method:

Add a new table, called "Translator", show as the bottom table:

Enter the data in columns B anc C as shown

cell A1 contains:

"=CODE(B1)"

select cell A1 and fill down

For the table on top enter your code in column A. The converted code will be in column B. Make sure there are enough columns (I went out to column AD):

B2=R2&S2&T2&U2&V2&W2&X2&Y2&Z2&AA2&AB2&AC2&AD2

C2=LEN(A2)

D2=MID($A2, COLUMN()−2, 1)

select D2, copy,

select cells D2 thru P2, paste

R2=IFERROR(VLOOKUP(CODE(D2), Translator::$A:$C, 3, 0), "")

select R2, copy,

select cells R2 thru AD2, paste

select B2 thru AD2 and fill down as needed

You can hide columns C thru AD

Reply

Answer 2

Badunit

Level 7

30,954 points

Dec 19, 2013 12:56 PM in response to hughmacfarlane

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SU BSTITUTE(SUBSTITUTE(A,"c","1"),"b","2"),"a","3"),"A","c"),"B","b"),"C","a"),"1", "A"),"2","B"),"3","C")

It replaces c,b,a with 1,2,3 (placeholders) then A,B,C with c,b,a and finally replaces 1,2,3 with A,B,C

Reply