Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Too Long Errors
Hi,
I have a spreadsheet with relatively large product descriptions (up to 40 lines or so) that are formatted with line breaks. I need to replace the line breaks with HTML code so they will display properly when uploaded to a website. To do this, I am trying to find/replace "ALT010" (line break) with <br. This works for the shorter descriptions, but many are giving me a "formula too long" error. How can I fix this? If no fix using the find/replace function, I am open to suggestions on how to get these formatted descriptions into an HTML format. THANKS! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Too Long Errors
Saved from a previous post:
You can use a macro to do the change: Option Explicit Sub testme01() Dim FoundCell As Range Dim ConstCells As Range Dim BeforeStr As String Dim AfterStr As String BeforeStr = vblf AfterStr = "<br" With ActiveSheet Set ConstCells = Nothing On Error Resume Next Set ConstCells = .Cells.SpecialCells(xlCellTypeConstants, _ xlTextValues) On Error GoTo 0 If ConstCells Is Nothing Then MsgBox "Select some cells in the used range" Exit Sub End If With ConstCells 'get as many as we can in one step .Replace what:=BeforeStr, Replacement:=AfterStr, _ lookat:=xlPart, SearchOrder:=xlByRows Do Set FoundCell = .Cells.Find(what:=BeforeStr, _ after:=.Cells(1), _ LookIn:=xlValues, _ lookat:=xlPart, _ SearchOrder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) If FoundCell Is Nothing Then 'done, get out! Exit Do End If FoundCell.Value _ = Replace(FoundCell.Value, BeforeStr, AfterStr) Loop End With End With End Sub If you're using xl97, change that Replace() to application.substitute() If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ps. Try it against a copy of your data--just in case! DBC wrote: Hi, I have a spreadsheet with relatively large product descriptions (up to 40 lines or so) that are formatted with line breaks. I need to replace the line breaks with HTML code so they will display properly when uploaded to a website. To do this, I am trying to find/replace "ALT010" (line break) with <br. This works for the shorter descriptions, but many are giving me a "formula too long" error. How can I fix this? If no fix using the find/replace function, I am open to suggestions on how to get these formatted descriptions into an HTML format. THANKS! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Errors | Excel Discussion (Misc queries) | |||
Formula errors | Excel Worksheet Functions | |||
Excel Throwing Circular Errors When No Errors Exist | Excel Worksheet Functions | |||
Formula errors | Excel Discussion (Misc queries) | |||
Unresolved Errors in IF Statements - Errors do not show in results | Excel Worksheet Functions |