trim to a certain comma from a known point

[The Idea Dude]

Hi all,

I have a HUGE amount of info in a cel. I am aiming to do a trim function to extract certain info. I know that from the start of the text in the cell (cell representing 1 cell that i need to do this for multiple cells) that the first instance of QLD is my reference point. From this reference point I desire to move 4 commas to the left.

How would i do this?

Cheers

 

[NBVC]

Please post an example Cell and your desired result.

Perhaps:

BookDec20.xls
	A	B	C	D
1		jason, mack, rick, mark, dog, QLD, Smith, black
2
3		jason, mack
4
Sheet1

Formula in B3: =LEFT(B1,SEARCH("#",SUBSTITUTE(B1,",","#",LEN(LEFT(B1,SEARCH("QLD",B1)-1))-LEN(SUBSTITUTE(LEFT(B1,SEARCH("QLD",B1)-1),",",""))-3))-1)

This pulls everything up to the 4th comma prior to the string "QLD"

Just adjust the final #3 in the formula to move to the next comma, if I caught the wrong one.

Is this what you want?

 

[sleepzz]

You could nested Finds to discover where you wanted to start so for example if you want to find the 4th comma after QLD you could use this:

=FIND(",",D5,(FIND(",",D5,(FIND(",",D5,FIND(",",D5,FIND("qld",D5))+1))+1))+1)

Not the tidiest so I'm sure someone will come up with something better

 

[The Idea Dude]

Thanks, Works a treat