Find and Replace: Formula is Too Long

[RJORDANW]

First of all, I know nothing of excel or VBA with excel. I'm using the record function to create several macros. This is the only thing I haven't been able to figure out. I'm trying to use the Search and Replace function to find cells and replace all of the data in the cell with a simple acronym; the letters PI. The data in the cells I'm searching for is as follows:

C-Type--Freddie MAC Property Insp-PFC -- A minimum of 2 photographs of the property which should include 1.Front view photo of the property 2.Photo confirming the address- property number/ property number on the mail box.If the property is vacant,1.Visual exterior identification if the property requires any maintenance/preservation on the exteriors. All maintenance required on the property should be captured in detail and supported with photos.Examples:A.Exterior Trash (estimate units) ,B.Yard maintenance (estimate lot size), C.Securing of property (lock box or lock required),2.Identify if there are any code violations notice posted on the property (provide photos).3.Identify if any health and safety issues on the property (provide photos).4.Assess the overall condition of the property (E.g. Distressed, Poor, Good, Fair?)If the property is found to be occupied 1. Front view photo of the property,2.Photo confirming the address- property number/ property # on the mail box/street sign if no property number on home or mailbox,3.Identify if there are any code violations notice posted on the property(provide photos),4.Identify if any health and safety issues on the property. (provide photos),5.Assess the overall condition of the property (E.g. Good, Fair?)

I'm trying to search for *Freddie* and replace with PI, with "Match entire cell contents" selected. Every time I do, I get the "Formula is too long" error. Is there any way to make this work, or reduce the data before running the search?

[stnkynts]

What is this?

C-Type--Freddie MAC Property Insp-PFC -- A minimum of 2 photographs of the property which should include 1.Front view photo of the property 2.Photo confirming the address- property number/ property number on the mail box.If the property is vacant,1.Visual exterior identification if the property requires any maintenance/preservation on the exteriors. All maintenance required on the property should be captured in detail and supported with photos.Examples:A.Exterior Trash (estimate units) ,B.Yard maintenance (estimate lot size), C.Securing of property (lock box or lock required),2.Identify if there are any code violations notice posted on the property (provide photos).3.Identify if any health and safety issues on the property (provide photos).4.Assess the overall condition of the property (E.g. Distressed, Poor, Good, Fair?)If the property is found to be occupied 1. Front view photo of the property,2.Photo confirming the address- property number/ property # on the mail box/street sign if no property number on home or mailbox,3.Identify if there are any code violations notice posted on the property(provide photos),4.Identify if any health and safety issues on the property. (provide photos),5.Assess the overall condition of the property (E.g. Good, Fair?)

[shg]

From the Excel UI, Find only works on cells with fewer than 256 characters. What you want can be done with VBA, but I'd suggest that you copy it to Word, do what you need there, and then copy it back.

[FDibbins]

Hi and welcome to the forum

If I understand you correctly, you have a text string as shown in your 2nd paragraph? (C-Type--Freddie.......Good, Fair?)...and want to replace Freddie with PI

If thats the case, then Im not sure what error message you are getting, but if you do F/R using this...

I'm trying to search for *Freddie* and replace with PI

That will replace the entire text string with PI (I just tried it).

Try just a plain (find) Freddie...(replace) PI

Edit: from shg...

From the Excel UI, Find only works on cells with fewer than 256 characters.

just noticed OP profile says 2002, it worked fine in 2007

[shg]

My bad -- I just tried in in 2003 and it worked also. I had recalled you couldn't do it prior to 2007. Musta just made that up.

[RJORDANW]

What is this?

I do inspections for mortgage companies. This is the instructions I receive for certain jobs. However, I don't need all of that data. I've seen it so many times I've practically memorized it. I just need PI to remind me of the job type.

[RJORDANW]

Hi and welcome to the forum

If I understand you correctly, you have a text string as shown in your 2nd paragraph? (C-Type--Freddie.......Good, Fair?)...and want to replace Freddie with PI

If thats the case, then Im not sure what error message you are getting, but if you do F/R using this...


That will replace the entire text string with PI (I just tried it).

Try just a plain (find) Freddie...(replace) PI

Edit: from shg...
just noticed OP profile says 2002, it worked fine in 2007

It is my goal to replace the entire string with the letters PI. Perhaps I will just have to get a newer version of excel. Thanks for the reply.

[RJORDANW]

From the Excel UI, Find only works on cells with fewer than 256 characters. What you want can be done with VBA, but I'd suggest that you copy it to Word, do what you need there, and then copy it back.

How can I do it with VBA? I'm writing a macro to take a list of hundreds of jobs, all with cells like this one, and convert them to a few letters (e.g. PI) so that I fit it on a sheet of paper. The macro saves me an hour a night. The code that I am using to attempt it with this cell is as follows:

Please Login or Register  to view this content.

Any help would be greatly appreciated.

[RJORDANW]

I upgraded to 2010. That resolved the problem. Thanks, everybody.