separating a text column into three

[yomoko]

Narayan:

I had to do a project today that went along the same lines you solved - Thanks a ton!!

However I seem to get the wrong result when entering this text "300-150 CC F 3 5-5 6 AX-S DV GR ED HI-RES BULB SEARCH BY LEGEND OR ITEM" - it cuts off after the word "or".

Can you help me? It looks like it always is deleting the last word.

-Thanks

 

[SirJB7]

@NARAYANK991

Hi!

From chandoo's website to the FBI most wanted it seems to be a short step. You're a trending topic today

Regards!

 

[NARAYANK991]

Hi Yomoko ,


My mistake. We need to force an extra space at the end of the text string , by concatenation.


Try this :


=MAX(((IFERROR(SEARCH(" ",$A$1&" ",ROW(INDIRECT(B5&":"&LEN($A$1&" ")))),999)<30+B5)*(ROW(INDIRECT(B5&":"&LEN($A$1&" "))))))


The addresses will change depending on where you put this formula ; in B4 , I start with 1 ; this formula is in B5 ; copy it downwards.


Narayan

 

[NARAYANK991]

Hi Bob ,


The uploaded file is here :


https://docs.google.com/open?id=0B0KMpuzr3MTVcDFTY29jMjJMeVk


Narayan

 

[yomoko]

Narayan,


Tanks for your help. However, I am getting a circular reference error. Can you please upload a sample file? It would be greatly appreciated!


Yomo

 

[bobhc]

Good day Narayan

File down loaded and saved, my thanks for your time