Separate Words from List Into Columns

Hi

Hope you can help me. I have a list in Column A. The list is a sample of a much longer one. Specific words or a combination of two words appear anywhere in the cell as you can see in column A. Column A is the original list. I'm looking to separate the list into three columns: The Outlet (B), the Charge Type(C) and either "Allowance" or "Disc"(D). In columns H, I and J I gave each range a dynamic name because if any word does not appear within those ranges they should not appear in columns B, C or D. I have left empty cells in each range so I can add words if the need arises.

Somehow I feel VLookup is the solution, but I could be wrong. I'll leave up to you.

I'd appreciate your feedback.

Many thanks

patish

Originally Posted by Phuocam

Try this in B2:

=IFERROR(LOOKUP(2,1/SEARCH($H$2:$H$6,$A2),$H$2:$H$6),"")

That can be reduced to:

=IFERROR(LOOKUP(1000,SEARCH($H$2:$H$6,$A2),$H$2:$H$6),"")

Hi Tony

Thanks for your quick response.

What is the 1000? Can't include blank cells?

Perfect solution.

Patish

Here's an explanation I wrote to a very similar question:

https://www.excelforum.com/showthread.php?p=4492401

Hi again

Thanks for the explanation, Tony. Tony, I noticed, when I looked further down the list that, after a certain rown the formula was throwing back blanks in all the columns where it should not have. Also, in the rows where at least one or two results were appearing there were others that returned blank.

I checked Phuocam formula and it worked perfectly. I'll be happy to send you examples of those sections that throw up incorrect results.

Thank you both for your feedbacks

Patish

Originally Posted by Patish

I'll be happy to send you examples of those sections that throw up incorrect results.

Yes, please.

Gentlemen

With my tail between my legs I grovel my apologies. I used both the formulaes next to each other and they both return the same results- PERFECTION WAS GUARENTEED!!! Don't know what happened there. My apologies, Tony. Is there no Post Icon that shows "dying of embarassment"? Whew, it's suddenly hot in here.

May I be so bold as to ask why in the example you linked, did you place the 1E100 instead of 1000?

Thanks both of you.

Patish

Originally Posted by Patish

May I be so bold as to ask why in the example you linked, did you place the 1E100 instead of 1000?

It was just a matter of convenience.

A cell can hold a maximum of 32,767 characters.

So, that means the SEARCH function will return either an error (when no keywords are found) or a number from 1 to 32,767.

So, the lookup value has to be a number that is larger than the number of characters in the cell(s).

Based on the data in your sample file the cells did not contain that many characters so I arbitrarily chose to use 1000 as the lookup value.

Both formulas do the exact same thing. The one I suggested goes about it slightly more efficiently. It takes one less calculation process to arrive at the result.

Thank you Guru.

You're welcome!