How to circumvent the 256 chars limitation on excel for search/replace actions? ?

Question

I am trying to perform a search/replace in excel 2003, but some of the fields have more than 256 character, returning the "formula too long" error.
I have more than 10.000 lines to change, so skipping is not actually a solution.
does someone know if a macro can be used for this action, overcoming this limitation?

Answer 1

If your formula is linked to another workbook, then open that other workbook as well it will remove the complete path from the formula to give you some additional characters.



Also change the name of the worksheet to single character and once finished with the replace then rename it back to original. Do the same thing with file name.

Answer 2

Replace Character In Excel

Answer 3

never realized that excel had a find/replace limitation. I just tried it and there is a limitation, but interestingly enough, the limitation on mine seems to be 891 characters (I'm using excel 2003 as well). If I have 892 characters I get the "formula too long" error. But 891 characters is ok.



short of writing a vba/macro (which I'm not sure if the limitation would apply to that either) the only suggestion I have is to break down the cells into smaller chunks, do the find/replace, then "reassemble" the chunks back to one large cell again. It wouldn't be that hard to do, assuming the data you're finding is all in the same column.



first, determine the longest one you have (you can use =LEN(cell ref) to see what the max length is.



so lets say your stuff starts in A1 and largest in that column is 1000

in B1 enter

=LEFT(A1, 256)

in C1 enter

=MID(A1, 257, LEN(A1))

in D1 enter

=MID(C1, 257, LEN(A1))

and continue doing this in E1, F1, etc until you've broken down A1 into 256 size chunks.



then copy > paste special and select "values" and paste the "values" back on top of itself (so we don't have formulas anymore, we just have all our text broken down in the cells)



copy these formulas down as far as you need to.



do your find/replace



then combine all the chunks back again

=B1&C1&D1&E1....etc



then copy > paste special > values again and paste the data back on top of your original data.



messy, but it would work. But if your data is spread all over and not all in one column, this technique could get even messier.



sorry I don't have a simpler solution

Answer 4

Here's the limits I found for Excel from the Excel help:



Feature Maximum limit

Open workbooks Limited by available memory and system resources

Worksheet size 65,536 rows by 256 columns

Column width 255 characters

Row height 409 points

Page breaks 1000 horizontal and vertical

Length of cell contents (text) 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar.

Sheets in a workbook Limited by available memory (default is 3 sheets)

Colors in a workbook 56

Cell styles in a workbook 4,000

Named views (view: A set of display and print settings that you can name and apply to a workbook. You can create more than one view of the same workbook without saving separate copies of the workbook.) in a workbook Limited by available memory

Custom number formats Between 200 and 250, depending on the language version of Excel you have installed.

Names in a workbook Limited by available memory

Windows in a workbook Limited by system resources

Panes in a window 4

Linked sheets Limited by available memory

Scenarios (scenario: A named set of input values that you can substitute in a worksheet model.) Limited by available memory; a summary report shows only the first 251 scenarios

Changing cells in a scenario 32

Adjustable cells in Solver 200

Custom functions Limited by available memory

Zoom range 10 percent to 400 percent

Reports Limited by available memory

Sort references 3 in a single sort; unlimited when using sequential sorts

Undo levels 16

Fields in a data form 32

Custom toolbars in a workbook Limited by available memory

Custom toolbar buttons Limited by available memory





Some important things you might want to note that are probably affecting you:



1. Column width 255 characters

2. Length of cell contents (text) 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar.



So if you only have one line it will fit only 255 characters. If you have multiple lines Excel 2003 will display up to 1,024 characters. It will hold up to 32,767 characters in each cell though, but only the first 1,024 will be displayed.

Answer 5

My reasoning relies on the subject it incredibly is here on the earth. If different lifeforms are obtainable, they're at a Pre clever point. Why? because of the fact the devil made his pass here, on the earth. and then he became able to effect different angels to pass away their provider to God and soak up bodies to have intercourse with the girls persons. That makes it sparkling to me that at this 2d, there is not any one like us obtainable that could have been interesting sufficient to get the attention that the angels gave the girls persons. considering the fact that the different lifeforms on the earth did not get that interest, it would be that people have a likeness that the angels ought to narrate to, as being loose ethical brokers like them. What Jehovah God has in techniques for relax of the Universe, we are able to could wait and notice.