7 nested functions alternative - substitute function

[bobby999]

As you can see i basically want to substitute the following "special" characters for an underscore. i need to do 9 different characters!!! is there another way?

as you are limited to 7!!!

excel 2003!




=INDIRECT("_animal_"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2," ","_"),"/","_"),"-","_"),"&","_"),"~","_"), "(", "_"), ")", "_"), "$", "_"), ":", "_"))

 

[Weaver]

Here's a UDF macro solution - in VB, paste into an available module and use in your sheet as a normal function
arg1 = string to be altered
arg2 = string containing characters to be replaced
arg3 = string containing character to replace with

Function MULTISUB(ByVal strStart As String, subs As String, sub4 As String) As String
    MULTISUB = ""
    For idx = 1 To Len(strStart)
        m = Mid(strStart, idx, 1)
        If InStr(subs, m) = 0 Then
            MULTISUB = MULTISUB & m
        Else
            MULTISUB = MULTISUB & sub4
        End If
    Next
End Function

 

[Weaver]

PS no doubt there's a real slick formula approach...

 

[bobby999]

=INDIRECT("_Service_"&MULTISUB(MULTISUB(MULTISUB(MULTISUB(MULTISUB(MULTISUB(MULTISUB(MULTISUB(MULTISUB(C2," ","_"),"/","_"),"-","_"),"&","_"),"~","_"), "(", "_"), ")", "_"), "$", "_"), ":", "_"))


like that?

it still has problem of more than 7nested functions?

 

[Krishnakumar]

Hi,

Select C2,

Hit Ctrl+F3,

Define Str

refers to: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet4!$C2,",","_")," ","_"),"/","_"),"-","_"),"&","_"),"~","_"),"(","_")

adjust the sheet name

In D2,

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Str,")","_"),"$","_"),":","_")

HTH

 

[bobby999]

Hi,

Select C2,

Hit Ctrl+F3,

Define Str

refers to: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet4!$C2,",","_")," ","_"),"/","_"),"-","_"),"&","_"),"~","_"),"(","_")

adjust the sheet name

In D2,

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Str,")","_"),"$","_"),":","_")

HTH

i dont think this willl work for me.

I am putting this formula in to data validation in d2.

it uses "_service_"& value in cell c2... to lookup another defined list.

i need to replace the characters taken from c2 as defined list do not take them and they have been replaced!!!

 

[bobby999]

i think i need a function for the following

Function MULTISUB( arg1 (celll where value is) )

For each charachter in string arg1
If charachter == " " or "/" or "-" or "&" or "$" or "~" or "(" or ")" or ":"
Then REPLACE character with "_"


so i can then do

=MULTISUB(C1)

which would replace "hello(man)and-you" with "hello_man_and_you"

 

[GlennUK]

i think i need a function for the following

Function MULTISUB( arg1 (celll where value is) )

For each charachter in string arg1
If charachter == " " or "/" or "-" or "&" or "$" or "~" or "(" or ")" or ":"
Then REPLACE character with "_"


so i can then do

=MULTISUB(C1)

which would replace "hello(man)and-you" with "hello_man_and_you"

excelR8R already gave you that:
=MULTISUB(c1,"$:/() &-~#"," ")

 

[Norie]

Why not try a Select Case... structure?

Something like this perhaps?

For I = 1 To Len(arg1)
    Select Case Mid(arg1,I,1)
         Case " ", "/" , "-","&","$","~" ,"(", ")", ":" 
             MULTISUB = MULTISUB & "_"
          Case Else
              MULTISUB = MULTISUB & Mid(arg1,1,1)
    End Select
Next I

Note since I've got no idea what data you are dealing with this has only been lightly tested.

 

[Krishnakumar]

i dont think this willl work for me.

I am putting this formula in to data validation in d2.

it uses "_service_"& value in cell c2... to lookup another defined list.

i need to replace the characters taken from c2 as defined list do not take them and they have been replaced!!!

Hi,

Again select C2,

hit CTRL+F3

define String

refers to:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Str,")","_"),"$","_"),":","_")

Select D2 > Data validation > List

=INDIRECT("_Service_"&String)

HTH