trim to a certain comma from a known point

The Idea Dude

Well-known Member
Joined
Aug 15, 2002
Messages
585
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
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
Please post an example Cell and your desired result.

Perhaps:
BookDec20.xls
ABCD
1jason, mack, rick, mark, dog, QLD, Smith, black
2
3jason, 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

New Member
Joined
Dec 20, 2005
Messages
3
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,529
Messages
5,572,657
Members
412,481
Latest member
nhantam
Top