Refining deposited data

[jbrooks1988]

Hi

I have a report that I run which is pasted into a master spreadsheet, and one column includes naming conventions I use. When this data from the report is dumped into the master spreadsheet I need the naming convention to be altered in the same cell. I know I can use a vlookup for this to refine the data in another cell but the report data is retrieved using a macro, and in specific order and I cant add extra columns to either my master spreadsheet or my report. I'm thinking an array formula is required, but I could be wrong - either way I'm not sure how this is done!

The naming convention is dumped into column F in the report, and is pasted along with 16 other columns simultaneously into the spreadsheet (column F data is now in column E).

Here is an example of the naming conventions I use, and what I need the data to be stripped down to (new result in bold):

Original

Jack Daniels (Red) Jack Daniels
Jack Daniels (Blue) Jack Daniels
John Smith (Jim Beam - Green) Jim Beam
John Smith (Johnny Walker - White) Johnny Walker

Thanks for your time

[Moo the Dog]

Assuming your list is in cells A1:A4, try this in B1 and copy down:

=IFERROR(MID(A1,FIND("(",A1)+1,FIND("-",A1)-FIND("(",A1)-2),LEFT(A1,FIND("(",A1)-2))

Of course, this is simply based on the two naming conventions displayed above. if there are others, then you would likely need a defined list of 'Raw Data' --> 'Name you want to use', and then lookup form there.

- Moo