Multiple input data , multiple output data

[Guest]

Hy,
I need an formula to solve my problem.
Let's say I have an cell with an text, for example : "AAJFHEFKR<FSTEG"
And I want to replace for example AA with A(A), FK with F(K), ST with S(T). I've tried =IF(ISNUMBER(SEARCH("AA";A1;1));SUBSTITUTE(A1;AA;"A(A)");A1)and is working but I want to write an formula who will search AA and FK and ST and replace all of them, something like an input vector {"AA";"FK";"ST"} and an output vector {"A(A)";"F(K)";"S(T)"}.
In the case A1="AAJFHEFKR<FSTEG" the result would be "A(A)JFHEF(K)R<FS(T)EG"
If A1 is "BB" the result will be A1 unchanged, so would be "BB".
If A1="1234AA456rfrST" ----> ""1234A(A)456rfrS(T)" .......

P.S: Sorry for my english.

 

[Biff]

Hi Me!

Try this:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"ST","S(T)"),"FK","F
(K)"),"AA","A(A)")

Note that this is case sensitive. No need for the IF
ISNUMBER SEARCH stuff.

Biff

-----Original Message-----
Hy,
I need an formula to solve my problem.
Let's say I have an cell with an text, for example : "AAJFHEFKR<FSTEG"
And I want to replace for example AA with A(A), FK with F

(K), ST with S(T). I've tried =IF(ISNUMBER(SEARCH
("AA";A1;1));SUBSTITUTE(A1;AA;"A(A)");A1)and is working
but I want to write an formula who will search AA and FK
and ST and replace all of them, something like an input
vector {"AA";"FK";"ST"} and an output vector {"A(A)";"F
(K)";"S(T)"}.

 

[Guest]

Ihave faund something with nested substitute :
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1;"AA";"A(A)");"FK";"F(K)");"ST";"S(T)") but there are some limits. I can't use more then 8 nested substitute because i'm getting errors from excel and I have 40 cases

 

[Guest]

Hy Biff. I have tried this but there is an limit of excel. I can't use more then 8 nested substitute. And I need 40 of them . Can you help me with this limit ?

 

[Biff]

Quote: I have 40 cases

LOL!

Good luck!

-----Original Message-----
Ihave faund something with nested substitute :
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1;"AA";"A(A)");"FK";"F

(K)");"ST";"S(T)") but there are some limits. I can't use
more then 8 nested substitute because i'm getting errors
from excel and I have 40 cases F(K), ST with S(T). I've tried =IF(ISNUMBER(SEARCH
("AA";A1;1));SUBSTITUTE(A1;AA;"A(A)");A1)and is working
but I want to write an formula who will search AA and FK
and ST and replace all of them, something like an input
vector {"AA";"FK";"ST"} and an output vector {"A(A)";"F
(K)";"S(T)"}.

 

[Biff]

Hi Me!

A VBA solution is probably needed but I can't help you
with that!

A clunky method would be to use seperate cells and use a
formula that checks for 5 or 6 "cases" at a time.

Biff

-----Original Message-----

Hy Biff. I have tried this but there is an limit of

excel. I can't use more then 8 nested substitute. And I
need 40 of them . Can you help me with this limit ?

 

[Guest]

That is what I have done : 8 cases at a time, 5 columns.

 

[Earl Kiosterud]

Me,

Here's a user-defined function (UDF):

Function Convert(InData As String) As String
Dim Celll As Range
Dim i As Integer
Dim Length As Integer
Length = Len(InData)
i = 0
Do
i = i + 1
For Each Celll In Sheets("Codes").Range("Codes")
If Mid(InData, i, 2) = Celll.Value Then
InData = Left(InData, i - 1) & Mid(InData, i, 1) & "(" & Mid(InData, i
+ 1, 1) & ")" & Mid(InData, i + 2, 9999)
i = i + 2
Length = Length + 2
Exit For
End If
Next Celll
Loop While i < Length - 1
Convert = InData
End Function


Paste it from here to a regular module, watching for line feeds added by the
post.

Call it in a cell, to wit: = Convert(A2)
Put your codes (AA, FK, etc.) in a sheet called Codes, and name the range
Codes.

It's thrown together. Test it thoroughly.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Hy,
I need an formula to solve my problem.
Let's say I have an cell with an text, for example : "AAJFHEFKR<FSTEG"
And I want to replace for example AA with A(A), FK with F(K), ST with

S(T). I've tried
=IF(ISNUMBER(SEARCH("AA";A1;1));SUBSTITUTE(A1;AA;"A(A)");A1)and is working
but I want to write an formula who will search AA and FK and ST and replace
all of them, something like an input vector {"AA";"FK";"ST"} and an output
vector {"A(A)";"F(K)";"S(T)"}.