limits to multiple subsitute funtion??

D

davidny26

Is there a limit to how many substitute functions I can have in one
formula? It does not seem to let me have more than 8. If there is a
limit, is there a work around?

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(BM2,"Oscar
Heyman","16431685"),"Buccelati","16431678"),"Cartier","16431674"),"Chanel","16431677"),"Chopard","16431686"),"David
Yurman","16431676"),"K. Cord","16431682"),"Krypell","16431683")

Thanks
 
P

Pete_UK

Yes, in Excel 2003 and earlier there is a limit of 7 nested functions.
One way round it is to continue the SUBSTITUTEs in another column, but
it might be better to use VLOOKUP if you want to change multiple names
into numbers or vice versa. Please explain what you are trying to do
and I'm sure someone will be able to suggest an alternative.

Hope this helps.

Pete
 
R

Rick Rothstein \(MVP - VB\)

As Pete_UK pointed out, yes, there is a limit. The following function will
do what your posted formula attempted to do...
 
R

Rick Rothstein \(MVP - VB\)

Damn, but I hate that Ctrl+Enter short-cut that posts a message before I'm
ready.

As Pete_UK pointed out, yes, there is a limit. The following function will
do what your posted formula attempted to do (accept it is not case sensitive
in the way SUBSTITUTE is)...

=INDEX({"16431685","16431678","16431674","16431677","16431686","16431676","16431682","16431683"},MATCH(BM2,{"Oscar
Heyman","Buccelati","Cartier","Chanel","Chopard","David Yurman","K.
Cord","Krypell"},0))

Usually, I wouldn't think SUBSTITUTE would be used the way you attempted to
use it... in my experience, SUBSTITUTE is usually used internally within a
string of text in order to change it in some way.

Rick



Yes, in Excel 2003 and earlier there is a limit of 7 nested functions.
One way round it is to continue the SUBSTITUTEs in another column, but
it might be better to use VLOOKUP if you want to change multiple names
into numbers or vice versa. Please explain what you are trying to do
and I'm sure someone will be able to suggest an alternative.

Hope this helps.

Pete
 
R

Rick Rothstein \(MVP - VB\)

Damn, but I hate that Ctrl+Enter short-cut that posts a message before I'm
ready.

As Pete_UK pointed out, yes, there is a limit. The following function will
do what your posted formula attempted to do (accept it is not case sensitive
in the way SUBSTITUTE is)...

=INDEX({"16431685","16431678","16431674","16431677","16431686","16431676","16431682","16431683"},MATCH(BM2,{"Oscar
Heyman","Buccelati","Cartier","Chanel","Chopard","David Yurman","K.
Cord","Krypell"},0))

Usually, I wouldn't think SUBSTITUTE would be used the way you attempted to
use it... in my experience, SUBSTITUTE is usually used internally within a
string of text in order to change it in some way.

Rick
 
R

Rick Rothstein \(MVP - VB\)

Sorry Pete... I ended up screwing this up a second time by posting my
completed message under your posting instead of mine.

{Damn! This is looking like its not going to be a good day for me.<g>}

Rick
 
A

Alexander Wolff

As Pete_UK pointed out, yes, there is a limit. The following function will
do what your posted formula attempted to do (accept it is not case
sensitive in the way SUBSTITUTE is)...

Usually, I wouldn't think SUBSTITUTE would be used the way you attempted
to use it... in my experience, SUBSTITUTE is usually used internally
within a string of text in order to change it in some way.
Click to expand...

Probably you assume right, but it should be mentioned that if BM2 is a long
string with _several_ appearances of creative designers of which _all_ have
to be treated, only nested SUBSTITUTE or VBA can solve this.
 
R

Rick Rothstein \(MVP - VB\)

And as Alexander pointed out, I more than likely got your intentions wrong
in my previous reply. If you are, in fact, substituting numbers for names
within some larger text string contained in a cell (BM2 as per your
posting), and if a VBA solution is acceptable, then here is a User Defined
Function that you should be able to use...

Function BigSubstitute(CellToChange As Range, _
ParamArray NameNumber()) As String
Dim X As Long
If (UBound(NameNumber) - LBound(NameNumber) + 1) Mod 2 Then
BigSubstitute = "#MISMATCH!"
Exit Function
Else
BigSubstitute = CellToChange.Value
For X = LBound(NameNumber) To UBound(NameNumber) Step 2
BigSubstitute = Replace(BigSubstitute, NameNumber(X), _
NameNumber(X + 1), , , vbTextCompare)
Next
End If
End Function

For your example, you would call it like this...

=BigSubstitute(BM2,"Oscar
Heyman","16431685","Buccelati","16431678","Cartier","16431674","Chanel","16431677","Chopard","16431686","David
Yurman","16431676","K. Cord","16431682","Krypell","16431683")

Also to be noted is that I created a #MISMATCH! error for this function if
the name/number pairing is mismatched.

By the way, if you are unfamiliar with how to implement a User Defined
Function, it is really pretty simple. In the VBA editor, click Insert/Module
from the menu bar and copy/paste my function above into the code window that
appears. That is it... you can now use BigSubstitute as if it were a built
in function.

Rick
 
R

Rick Rothstein \(MVP - VB\)

As Pete_UK pointed out, yes, there is a limit. The following function
Probably you assume right, but it should be mentioned that if BM2 is a
long string with _several_ appearances of creative designers of which
_all_ have to be treated, only nested SUBSTITUTE or VBA can solve this.
Click to expand...

You know, I think you are right... I'm not sure why I saw it differently
when I first viewed the OP's posting, but I now think BM2 probably does
contain text in it other than just a single person's name... which, of
course, means my formula will not do what the OP wanted. I just posted a UDF
for the OP to consider instead. Hopefully, that will be what he needs.
Thanks for posting your comment.

Rick
 
D

davidny26

Thank you Rick! Your VBA formula works brilliantly! I see you are an
avid user on this group. May I ask another question - I am uploading
a tab-delimited file on excel, and the cell must be data on a single
line with no line breaks, I do remove the line breaks using a find an
replace on word (replace ^p with ^s) but when I paste the data in a
cell, save, and reopen, some of the contents of the cell appears in
another cell. This briefly stopped occuring, don't know what I did
before to make it stop, anyone with any ideas?
thanks
 
D

davidny26

Hi Rick, regarding my last post, you can ignore it, but regarding your
Bigsubstitute formula, it works great, but is there a limit? It seems
that it is allowing me only 13 subsitutes. I am doing soething wrong?
thakns
 
R

Rick Rothstein \(MVP - VB\)

Hi Rick, regarding my last post, you can ignore it, but regarding your
Bigsubstitute formula, it works great, but is there a limit? It seems
that it is allowing me only 13 subsitutes. I am doing soething wrong?
Click to expand...

The function doesn't really have a limit.. a parameter array can be as large
as one wants. However, there is a limitation I forgot about... Excel has a
limit of 30 arguments in a formula... I'm guessing you are bumping up
against that. Okay, so let's change the function to get around this problem.
Use this function in place of the one I posted earlier...

Function BigSubstitute(CellToChange As Range, _
NameNumber As String) As String
Dim X As Long
Dim Data() As String
Data = Split(NameNumber, ",")
If (UBound(Data) + 1) Mod 2 Then
BigSubstitute = "#MISMATCH!"
Exit Function
Else
BigSubstitute = CellToChange.Value
For X = 0 To UBound(Data) Step 2
BigSubstitute = Replace(BigSubstitute, Data(X), _
Data(X + 1), , , vbTextCompare)
Next
End If
End Function

Note that your data entry is no longer individual strings... the list of
text and substitutions is now one long string (a quote at the beginning and
end only; no internal quote marks). Also, the comma separated list cannot
have any spaces inserted to neaten up the look of the list... if you put a
space after a comma, that space will be considered part of the text it is
next to. Here is what your original sample data would look like when it is
entered for this new function...

=BigSubstitute(A1,"Oscar
Heyman,16431685,Buccelati,16431678,Cartier,16431674,Chanel,16431677,Chopard,16431686,David
Yurman,16431676,K. Cord,16431682,Krypell,16431683")

Give this all a try and let me know if it works for you.

Rick
 
D

davidny26

Rick,
Thank you again for your time. This new formula sees to limit to only
10 substitutions. I am quite sure I am using it correctly. But you
said it limits to 30 arguments in a formula? That would be fine for me
it it worked.
Thanks
Ron
 
Top