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 did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

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,123,227
Messages
5,600,414
Members
414,383
Latest member
kevinlarey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top