# Search w/in text (complicated)

#### JR Taylor

##### New Member
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
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
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
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:

Replies
9
Views
185
Replies
8
Views
149
Replies
3
Views
132
Replies
0
Views
35
Replies
12
Views
669

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.

### Which adblocker are you using?

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

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