Substitute Nesting Limit Reached (Nested 64 Substitute Formulas)

[storm9]

Hi, I've been trying to find an alternative to the substitute function where im not limited to the 64 limit nesting rule. At the moment my current forumla has hit the cap. I've tried using vlookup with a table but it doesn't give me the same desired result.

What the forumla does is this, in one coloumn I have a bunch of text that isn't formatted properly and by using the proper function it puts all the text in the proper case format but I need certain words in uppercase or lowercase, hence the need for the substitute formula. Now in the adjacent column to the text I have the below formula and it would make the necessary corrections for me and would output the full string as I pasted it in.

The list of words that would need changing would grow over time which is why i've already hit the 64 formula nesting limit. I tried the table method as that would have been easier to add to later on. I haven't really done any UDF stuff so I don't really know where to start with it.

Example Data:

Input Output
Column A Column B
hi, i i Am sAMPLE DATA Hi, I Am Sample Data
tHis is From ME This is from Me
pLease heLp Please Help


Original Formula:

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(PROPER(B7), "Ul>", "ul>"), "P>", "p>"), "From", "from"), " The ", " the "), " And ", " and "), " To ", " to "), " A ", " a "), " Is ", " is "), " As ", " as "), "Li>", "li>"), "Cm", "cm"), "Mm", "mm"), "Led", "LED"), "Of", "of"), " Or ", " or "), "&Nbsp;", ""), "Kg", "kg"), "Vesa", "VESA"), "Lcd", "LCD"), " X ", " x "), "Tv", "TV"), "At ", "at "),"Dc", "DC"), "Ac ", "AC "), "Usb", "USB"), "Fm", "FM"), " Dab ", " DAB "),"> ",">")," <","<"),"> ",">")," <","<"),"> ",">")," <","<"),"> ",">")," <","<"),"> ",">")," <", "<"),"> ",">"),"> ",">")," <","<"),"> ",">")," <","<"),"> ",">")," <","<"),"> ",">")," <","<"),"> ",">")," <", "<"),"> ",">"),"&Amp;", "&"),"Center", "Centre"),"Color", "Colour"), "Aluminum", "Aluminium"),"'S ", "'s "),"Mdf", "MDF"),"Strong>", "strong>"),"Gsm", "GSM"),"&Deg;", "°"),"&Ndash;", "–"),"Ltr", "ltr"), "Cc", "cc"),"Hp", "hp"),"Rpm", "RPM"))


Any help would be much appreciated, Im not sure how to attach spreadsheets (is possible) so apologies if the post is a little messy.


Thanks in advance

 

[TheVillageIdiot]

How many items need to be proper case? If you do something like =LOWER(TRIM(....)) you may be able to preserve a formula call rather than VBA if the proper case items require fewer changes. If there are too many of each, the table approach is the way to go.

 

[Peter_SSs]

How do I get that code to show the new value in D2 instead of just updating the value in B2?

Try these replacement lines of code

With Sheets("Sheet1")
  <del>With .Range("B2", .Range("B" & .Rows.Count).End(xlUp))</del>
  With .Range("D2:D" & .Range("B" & .Rows.Count).End(xlUp).Row)
    <del>.Value = Evaluate(Replace("if(len(#),proper(#),"""")", "#", .Address))</del>
    .Value = Evaluate(Replace("if(len(#),proper(#),"""")", "#", .Offset(, -2).Address))
    For i = 1 To UBound(a)

 

[storm9]

Thanks that's perfect, exactly what I was after!

 

[Peter_SSs]

Thanks that's perfect, exactly what I was after!

Good news, thanks for letting us know.

 

[TheVillageIdiot]

Try these replacement lines of code

With Sheets("Sheet1")
  <del>With .Range("B2", .Range("B" & .Rows.Count).End(xlUp))</del>
  With .Range("D2:D" & .Range("B" & .Rows.Count).End(xlUp).Row)
    <del>.Value = Evaluate(Replace("if(len(#),proper(#),"""")", "#", .Address))</del>
    .Value = Evaluate(Replace("if(len(#),proper(#),"""")", "#", .Offset(, -2).Address))
    For i = 1 To UBound(a)

What does the Evaluate do here? It appears to be changing the formula in the reference cells, but the end result is not a formula but a text string. I achieved the same results with:

.Value = .Value

What am I missing?

 

[Peter_SSs]

What does the Evaluate do here? It appears to be changing the formula in the reference cells, but the end result is not a formula but a text string. I achieved the same results with:

.Value = .Value

What am I missing?

I'm not sure you are missing anything but to use .Value = .Value you first need to put a formula in all the rows of the worksheet to convert the original text to proper case. I was using Evaluate to do the conversion to proper case directly from the original data without first having the code actually enter the formulas on the worksheet. Looking back, I probably should have used the formula/value approach for this task (faster execution I think).

 

[TheVillageIdiot]

I'm not sure you are missing anything but to use .Value = .Value you first need to put a formula in all the rows of the worksheet to convert the original text to proper case. I was using Evaluate to do the conversion to proper case directly from the original data without first having the code actually enter the formulas on the worksheet. Looking back, I probably should have used the formula/value approach for this task (faster execution I think).

Got it. Thanks. On my brief test, I already had the =PROPER(#) formula in worksheet. Did not realize that you essentially added it at runtime.