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

 

[XOR LX]

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

 

[István Hirsch]

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]

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