Substitute Nesting Limit Reached (Nested 64 Substitute Formulas)

S

storm9

New Member
Joined
Jul 30, 2017
Messages
7
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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
record a find / replace macro and then replicate, make sure you replace double space to single space first and that might fix your < and > issues. substitute will only effect what you see it won't make real changes. You should also for the PROPER conversion first and then clean the text string without it
 
Last edited:
Upvote 0
Thanks for the reply and suggestion, i've tried it using a macro but it's messy as there are a lot of search parameters,

Is there a cleaner way of doing this?
 
Upvote 0
post your macro between code tags. CASE might be another way to go but I don't think so for this
 
Upvote 0
Not sure why my previous posts haven't appeared, in any case, i've more or less figured it out,

Only issue i'm having with the macro is, I have to click in the cell above where it will give me the text each time. Is there anyway to get the macro to output in those cells without having to click into a particular cell?

e.g. Text is in A2, and it would output in B2, I'd have to click in B1 each time.

Is it possible to have the macro always output into B2 regardless of what cell is selected? Or preferable automate it?


Thanks again!


Code: 
Sub Macro4()
'
' MainWorking Macro
'

'
    ActiveCell.FormulaR1C1 = "=PROPER(RC[-2])"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A27"), Type:= _
        xlFillDefault
    ActiveCell.Range("A1:A27").Select
    ActiveCell.Range("A1:A27").Select
    Selection.Copy
    ActiveCell.Offset(0, 2).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Range("A1:A27").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveCell.Offset(0, -2).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Select
    Application.CutCopyMode = False
    ActiveCell.Range("A1:A27").Select
    Cells.Replace What:="Rpm", Replacement:="RPM", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="From", Replacement:="from", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="The ", Replacement:="the ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="And ", Replacement:="and ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:=" To ", Replacement:=" to ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:=" Is ", Replacement:=" is ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:=" A ", Replacement:=" a ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="Cm", Replacement:="cm", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="Mm", Replacement:="mm", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="Led", Replacement:="LED", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="Ul>", Replacement:="ul>", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="P>", Replacement:="p>", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:=" As ", Replacement:=" as ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="Li>", Replacement:="li>", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:=" Of ", Replacement:=" of ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:=" Or ", Replacement:=" or ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="&Nbsp;", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="Kg", Replacement:="kg", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="Vesa", Replacement:="VESA", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="Lcd", Replacement:="LCD", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:=" X ", Replacement:=" x ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="Tv", Replacement:="TV", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="At ", Replacement:="at ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="Dc", Replacement:="DC", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="Ac ", Replacement:="AC ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="Usb", Replacement:="USB", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="Fm", Replacement:="FM", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="Dab", Replacement:="DAB", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="> ", Replacement:=">", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:=" <", Replacement:="<", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:=">  ", Replacement:=">", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="  <", Replacement:="<", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:=">   ", Replacement:=">", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="   <", Replacement:="<", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:=">    ", Replacement:=">", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="     <", Replacement:="<", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="> ", Replacement:=">", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:=" <", Replacement:="<", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="> ", Replacement:=">", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:=" <", Replacement:="<", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="> ", Replacement:=">", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:=" <", Replacement:="<", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="> ", Replacement:=">", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:=" <", Replacement:="<", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="> ", Replacement:=">", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:=" <", Replacement:="<", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="> ", Replacement:=">", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:=" <", Replacement:="<", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="> ", Replacement:=">", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:=" <", Replacement:="<", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="> ", Replacement:=">", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:=" <", Replacement:="<", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="> ", Replacement:=">", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:=" <", Replacement:="<", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="> ", Replacement:=">", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:=" <", Replacement:="<", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="> ", Replacement:=">", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:=" <", Replacement:="<", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="> ", Replacement:=">", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:=" <", Replacement:="<", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="> ", Replacement:=">", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:=" <", Replacement:="<", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="> ", Replacement:=">", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:=" <", Replacement:="<", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="&Amp;", Replacement:="&", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="Center", Replacement:="Centre", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="Color", Replacement:="Colour", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="Aluminum", Replacement:="Aluminium", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="'S ", Replacement:="'s ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="Mdf", Replacement:="MDF", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="Strong>", Replacement:="strong>", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="Gsm", Replacement:="GSM", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="&Deg;", Replacement:="°", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="&Ndash;", Replacement:="–", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="Ltr", Replacement:="ltr", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="Cc", Replacement:="cc", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="Hp", Replacement:="hp", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="Rpm", Replacement:="RPM", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    ActiveCell.Offset(0, 2).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=TRIM(RC[-2])"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A27"), Type:= _
        xlFillDefault
    ActiveCell.Range("A1:A27").Select
    ActiveCell.Range("A1:A27").Select
    Selection.Copy
    ActiveCell.Offset(0, -2).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 2).Range("A1:A27").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    ActiveCell.Offset(0, -2).Range("A1").Select
End Sub
 
Last edited by a moderator:
Upvote 0
Here is a variation that you could try to adapt to your circumstances.
In 'Sheet2' I have entered a (small) list of replacements, including the leading/trailing spaces, and turned it into a formal Excel Table. This way new replacements are easy to add and will automatically get picked up by the code.

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]
A​
[/th][th]
B​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
1​
[/td][td=bgcolor:#4F81BD]Old[/td][td=bgcolor:#4F81BD]New[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
2​
[/td][td=bgcolor:#B8CCE4]From[/td][td=bgcolor:#B8CCE4]from[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
3​
[/td][td=bgcolor:#DCE6F1] The [/td][td=bgcolor:#DCE6F1] the [/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
4​
[/td][td=bgcolor:#B8CCE4] And [/td][td=bgcolor:#B8CCE4] and [/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
5​
[/td][td=bgcolor:#DCE6F1] To [/td][td=bgcolor:#DCE6F1] to [/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
6​
[/td][td=bgcolor:#B8CCE4] A [/td][td=bgcolor:#B8CCE4] a [/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
7​
[/td][td=bgcolor:#DCE6F1] Is [/td][td=bgcolor:#DCE6F1] is [/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
8​
[/td][td=bgcolor:#B8CCE4] As [/td][td=bgcolor:#B8CCE4] as [/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
9​
[/td][td=bgcolor:#DCE6F1]Cm[/td][td=bgcolor:#DCE6F1]cm[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
10​
[/td][td=bgcolor:#B8CCE4]Mm[/td][td=bgcolor:#B8CCE4]mm[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
11​
[/td][td=bgcolor:#DCE6F1] Or [/td][td=bgcolor:#DCE6F1] or [/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
12​
[/td][td=bgcolor:#B8CCE4]Kg[/td][td=bgcolor:#B8CCE4]kg[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
13​
[/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet2[/td][/tr][/table]



In 'Sheet1' I have the raw, badly formatted text in column B, starting in row 2. My code to reformat that 'Sheet1' text is as follows. Is that something you can work with?

Code: 
Sub FixFormatting()
  Dim a As Variant
  Dim i As Long
  
  a = Sheets("Sheet2").ListObjects(1).DataBodyRange.Value
  Application.ScreenUpdating = False
  With Sheets("Sheet1")
    With .Range("B2", .Range("B" & .Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if(len(#),proper(#),"""")", "#", .Address))
      For i = 1 To UBound(a)
        .Replace What:=a(i, 1), Replacement:=a(i, 2), LookAt:=xlPart, MatchCase:=True
      Next i
    End With
  End With
  Application.ScreenUpdating = True
End Sub
 
Last edited:
Upvote 0
Hi and thanks, that's a massive help.

I've tried the code but I can't seem to get it to work.

I've typed text into column b but nothing happens, how do I run it? I keep getting an error.
 
Upvote 0
are you using sheet2 with the lookups, and sheet1 for your data to change, what is the error message
 
Upvote 0
I get a runtime error 9

Subscript out of range:

I've got my incorrect text in B2 down in sheet 1 and i've got sheet 2 with the lookups. With Old and New in A1 and B1 and then the words below those.

When i go to debug it highlights this line:

a = Sheets("Sheet2").ListObjects(1).DataBodyRange.Value


Thanks
 
Upvote 0
Thanks I was able to get this to work. My fault, forgot to make a formal table in excel.

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


Thanks Again
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
formula to return two columns of data adjacent to #N/A cell entry
Replies
3
Views
377
Fluff
Fluff
G
  • Question Question
IF AND nesting and how to avoid the 64 nest limit
Replies
3
Views
687
Fluff
Fluff
B
  • Question Question
Nesting limit reached. How to exceed past 64 nesting IF formula's.
2
Replies
13
Views
8K
Badge83
B

Forum statistics

Threads
1,224,819
Messages
6,181,153
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top