Separate out data (2003)

Topic

New Reply

I’ve attached a sample spreadsheet. I need to pull out the different office name in this spreadsheet to a separate column. The offices are WAN, PEC, MC, QC. Unfortunately, the location of the office in each record is not consistent. Sometimes it’s at the beginning, sometimes it’s at the end, and once in a while it’s even in the middle. This is a monthly data dump from our inventory program, and we can’t control how the data is dumped. So, this is something we will need to do each month.

Thank you!

Reply | Quote

Replies

How about the ARRAY function (conform with ctrl-shift-enter):
=INDEX({“WAN”,”PEC”,”MC”,”QC”,”PRD”},MAX(IF(ISNUMBER(FIND({“WAN”,”PEC”,”MC”,”QC”,”PRD”},A1)),{1,2,3,4,5})))

I added “PRD” since I presumed that “PRD” was also an office name from the line: “TYTON SEAL, PRD”. You can add more if desired in the 2 arrays and increase the number of values in the 3rd array.

Steve

Reply | Quote

Or, you can try this NON-ARRAY formula

B1 :

=LOOKUP(1,- FIND({“WAN”,”PEC”,”MC”,”QC”,”PRD”},A1),{“WAN”,”PEC”,”MC”,”QC”,”PRD”})

Regards
Bosco

Reply | Quote