Using a reference list with SEARCH and LEFT, RIGHT or MID?

[kje]

Hello...

I'm cleaning up poorly structured company information - company name, address etc... and I've been getting good results with SEARCH combined with LEFT, RIGHT or MID etc... to identify tokens and 'bring forward' only what I want to keep. I use IFERROR and the previous cell reference to ignore cases where my search isn't found. As I move through the columns, the data gets cleaner, then I put the different elements back together.

Now I'm getting into performance issues - I have many records and with upwards of 50 or 60 reference columns and growing (each building on the results of the previous) I'm really grinding to a halt.

Here is the formula I'm using for the example provided, with each reference token as the header of a column.

=IFERROR(LEFT(A2,SEARCH($B$1,A2)+LEN($B$1)-1),A2)

Is it possible to use a reference list on another sheet and maybe INDEX+MATCH?? to minimize the number of columns I'm referencing?

excel example.png

Thanks!!!

[JBeaucaire]

You most likely can, depending on what you actually have in mind. It's usually easiest to answer questions like this if you SHOW us what you're trying to accomplish.

Attach a sample workbook. Make sure there is enough sample data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if possible or practical. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic