Using IFERROR to search and replace

[FatPhil]

Hi All, apologies if this is solved elsewhere, I'm struggling to even describe the issue clearly so searches haven't helped.

I have a Salesforce report pulled into excel listing projects.
Within the project name exists the client name, entered as freetext within Salesforce.

This means I have fields named:
- Coca Cola - Project 1
- CocaCola - Project 2


I've written a formula to identify this and insert a consistent name in a new 'Client' column
=IFERROR(IF(SEARCH("*Coca Cola*",A2,1),"Coke"),"")
&IFERROR(IF(SEARCH("*CocaCola*",A3,1),"Coke"),"")

This works fine, but I've now added so many lines to the formula that I'm unable to save as xlsx (which I need for Power BI)

Is there a way to use a smaller formula with a VLOOKUP or something to allow me to use a much larger list of misnamed clients?


Thanks in advance for any help - I'm ok with excel but by no means great and entirely self taught.

[Richard Buttrey]

Do you have a master list of valid client names?

If so and assuming the " -" characters are consistent in separating the client from the project you could use the following formula to identify ones that don't match your list.

Please Login or Register  to view this content.

[FatPhil]

Richard, thanks for the reply.

I do have a master list of Client names, unfortunately the '-' is not consistent, I'm using the '*' at either end to find some identifiable block of text within the body

[JeteMc]

Hello FatPhil and Welcome to Excel Forum.
If I understand correctly then the following array entered formula* may be of some use: =INDEX(G$2:G$3,SUMPRODUCT((ROW(A$1:A$2))*(IFERROR(SEARCH(F$2:F$3,A2),0))))
*An array entered formula is confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
A list of the old names is in column F and the list new names is column G.
If this isn't helpful then perhaps uploading a small desensitized sample of your list and desired outcome will give us some understanding.
To upload a sample workbook (not a picture or pasted copy) click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
Let us know if you have any questions.