Separate Words from List Into Columns

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!