Search w/in text (complicated)

JR Taylor

New Member
Joined
Oct 16, 2014
Messages
6
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
 

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
Try this, put in B1 and copy down:

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

I though you, Rick Rothstein and I already went through the drawbacks inherent in this method several months ago!

One example string which will cause incorrect results is ABC###12E5CQW12

Regards
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

István Hirsch

Well-known Member
Joined
May 16, 2013
Messages
1,634
I though you, Rick Rothstein and I already went through the drawbacks inherent in this method several months ago!

One example string which will cause incorrect results is ABC###12E5CQW12

Regards

Yes, I remember.
The OP’s short description mentions that the number to extract is almost always followed by a space; in other cases the string you created may occur and my formula may need to be adapted to the structures that have remained unknown.

I think, a formula should be very very long if someone liked to make it applicable for all potential structures. For example, now the OP says he wants to extract the number following „###”, but he does not say that there are not other „###” strings elsewhere in the cell: formula #9 fails to extract the number from „###abc###123”.
I am afraid, we will never know that, in the present case, formula #10 is enough, or formula #9 or even more is needed.
 

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
The OP’s short description mentions that the number to extract is almost always followed by a space...

True. But then I can never take "almost always" as satisfactory!

I think, a formula should be very very long if someone liked to make it applicable for all potential structures. For example, now the OP says he wants to extract the number following „###”, but he does not say that there are not other „###” strings elsewhere in the cell: formula #9 fails to extract the number from „###abc###123”.

And I'm glad you've pointed out the above counter-example, which I simply hadn't considered. Though I disagree with your definition of "very, very long": here, for example:

Advanced Formula Challenge #7: Results and Discussion | EXCELXOR


I demonstrate a method which, in my opinion, is not what I would consider unfeasibly long, nor unnecessarily complex, and which would also seem to be readily adaptable to tackling the question given in this post. Indeed, if I have some time tomorrow I will gladly post a solution based on the technique outlined there.

I am afraid, we will never know that, in the present case, formula #10 is enough, or formula #9 or even more is needed.

Do you really think so? But can't we at least try? Isn't that part of the reason for forums such as this? So that people get together, who, via back-and-forth discussions such as this, may finally arrive at the truth, i.e. a solution which works for all examples that the OP may throw at us?

Regards
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,129,472
Messages
5,636,516
Members
416,920
Latest member
Riskyplan

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