Search w/in text (complicated)

[JR Taylor]

Hi all,

I'm looking to search the text w/in a cell for a certain string of characters. I have searched the forum and have successfully found a way to do this so that piece of the puzzle is solved, but my problem is a bit more complicated since some of the text will always be changing.

What I need to pull back are the digits that always follow "###". For example, sometimes it may read "###1", other times it may read "###138". I need the digits that follow the "###" whether it is one digit or more (I don't ever see there being any more than 4 digits if that helps).

Any thoughts on how I can get at this information?

JR

 

[ChrisBM]

=MID(D25,FIND("###",D25)+3,LEN(D25)-FIND("###",D25)+2)

Just repalce D25 with cell address

Hope this helps. Let me know if you have any questions.

Kind Regards,
Chris

 

[JR Taylor]

=MID(D25,FIND("###",D25)+3,LEN(D25)-FIND("###",D25)+2)

Just repalce D25 with cell address

Hope this helps. Let me know if you have any questions.

Kind Regards,
Chris

Chris - this is amazing. Thank you! It works like a charm. I wanted to do some quick testing and found 4 different scenarios.

1. If the only text in the cell is the ###123 it works great!
2. If there is text before the ###123 it works great!
3. If there is text after the ###123 it also pulls back the additional text (not so great)
4. If there is text before AND after the ###123 it also pulls back the text after (not so great)

Is there a way to get the formula to stop after the string of ###123 string?

JR

 

[ChrisBM]

What sort of text would indicate the end of the 123 string?

Are you only interested in the numeric elements?


Solution
I'm afraid this is going to get really very fiddly now:

=LEFT(MID(D25,FIND("###",D25)+3,LEN(D25)-FIND("###",D25)+2), MATCH(FALSE, ISNUMBER(1*MID(MID(D25,FIND("###",D25)+3,LEN(D25)-FIND("###",D25)+2), ROW(1:100),1)),0)-1)

You have to press Shift-Enter to enter the above as an Array Formula. Sorry, couldn't think of an easier way.

Essentially, the above formula is finding the string after the ### and then taking any digits before the first NON Number digit by cycling through the digits and seeing if they are a number. Max string length 100.

 

[JR Taylor]

What sort of text would indicate the end of the 123 string?

Are you only interested in the numeric elements?


Solution
I'm afraid this is going to get really very fiddly now:

=LEFT(MID(D25,FIND("###",D25)+3,LEN(D25)-FIND("###",D25)+2), MATCH(FALSE, ISNUMBER(1*MID(MID(D25,FIND("###",D25)+3,LEN(D25)-FIND("###",D25)+2), ROW(1:100),1)),0)-1)

You have to press Shift-Enter to enter the above as an Array Formula. Sorry, couldn't think of an easier way.

Essentially, the above formula is finding the string after the ### and then taking any digits before the first NON Number digit by cycling through the digits and seeing if they are a number. Max string length 100.

Chris - this works perfectly!

Man, I thought I was good at excel. Actually, I know I am, but breaking this down to understand it is above my level of knowledge.

To answer your question, there will almost always be a space but I can't ensure it. I texted your new formula with multiple test cases (space, no space, etc) and it worked every time.

Thanks again!

 

[JR Taylor]

Chris - this works perfectly!

Man, I thought I was good at excel. Actually, I know I am, but breaking this down to understand it is above my level of knowledge.

To answer your question, there will almost always be a space but I can't ensure it. I texted your new formula with multiple test cases (space, no space, etc) and it worked every time.

Thanks again!

Hey Chris,

Just following up. I ran weekly reporting using the function above and it actually didn't work but I believe I know why and i'm hoping you can advise further.

Quick overview:

I export data from our CRM database and drop the 'raw' data into a tab 2 of our master workbook. Since the data export column names could change without notice to our group, I have set up tab 1 using hlookups to pull over the data that I want. Hlookups ensure that no matter what column the data is in, as long as the column header matches, it will pull it.

I then do all my calculations on the configured data in Tab 1. This is also where I have set up the use of your function above. Unfortunately I believe this is why it's not working...because it's searching a function, and not the text results of my hlookup function.

I very much appreciate your previous assist and look forward to any additional help.

JR

 

[JR Taylor]

Hey Chris,

Just following up. I ran weekly reporting using the function above and it actually didn't work but I believe I know why and i'm hoping you can advise further.

Quick overview:

I export data from our CRM database and drop the 'raw' data into a tab 2 of our master workbook. Since the data export column names could change without notice to our group, I have set up tab 1 using hlookups to pull over the data that I want. Hlookups ensure that no matter what column the data is in, as long as the column header matches, it will pull it.

I then do all my calculations on the configured data in Tab 1. This is also where I have set up the use of your function above. Unfortunately I believe this is why it's not working...because it's searching a function, and not the text results of my hlookup function.

I very much appreciate your previous assist and look forward to any additional help.

JR

Doing some more investigating...

I updated your formula...am I missing something?

=IF(ISERROR(LEFT(MID(K2,FIND("###",K2)+3,LEN(K2)-FIND("###",K2)+2), MATCH(FALSE, ISNUMBER(1*MID(MID(K2,FIND("###",K2)+3,LEN(K2)-FIND("###",K2)+2), ROW(1:100),1)),0)-1)),1,LEFT(MID(K2,FIND("###",K2)+3,LEN(K2)-FIND("###",K2)+2), MATCH(FALSE, ISNUMBER(1*MID(MID(K2,FIND("###",K2)+3,LEN(K2)-FIND("###",K2)+2), ROW(1:100),1)),0)-1))

This, when entered as an array, is coming back BLANK whenever it finds ###. I have reviewed the raw data and I see some that are ###1, ###7, ###12...etc, and anytime the ### is present, the foluma pulls back blank.

JR

 

[JR Taylor]

Doing some more investigating...

I updated your formula...am I missing something?

=IF(ISERROR(LEFT(MID(K2,FIND("###",K2)+3,LEN(K2)-FIND("###",K2)+2), MATCH(FALSE, ISNUMBER(1*MID(MID(K2,FIND("###",K2)+3,LEN(K2)-FIND("###",K2)+2), ROW(1:100),1)),0)-1)),1,LEFT(MID(K2,FIND("###",K2)+3,LEN(K2)-FIND("###",K2)+2), MATCH(FALSE, ISNUMBER(1*MID(MID(K2,FIND("###",K2)+3,LEN(K2)-FIND("###",K2)+2), ROW(1:100),1)),0)-1))

This, when entered as an array, is coming back BLANK whenever it finds ###. I have reviewed the raw data and I see some that are ###1, ###7, ###12...etc, and anytime the ### is present, the foluma pulls back blank.

JR

ARG! Figured it out!

It had to do with not locking the "ROW(1:100)". These numbers were changing when I dragged the formula down. Fixed. Please consider this thread resolved.

 

[XOR LX]

Perhaps this array formula** will also work for you:

=MID(A1,FIND("###",A1)+3,MATCH(FALSE,ISNUMBER(0+MID(MID(A1,FIND("###",A1),999),ROW(INDIRECT("4:999")),1)),0)-1)

Regards

 

[István Hirsch]

Hi all,

I'm looking to search the text w/in a cell for a certain string of characters. I have searched the forum and have successfully found a way to do this so that piece of the puzzle is solved, but my problem is a bit more complicated since some of the text will always be changing.

What I need to pull back are the digits that always follow "###". For example, sometimes it may read "###1", other times it may read "###138". I need the digits that follow the "###" whether it is one digit or more (I don't ever see there being any more than 4 digits if that helps).

Any thoughts on how I can get at this information?

JR

Try this, put in B1 and copy down:

=IFERROR(-LOOKUP(0,-MID(A1,FIND("###",A1)+3,{1,2,3,4})),"")