Extracting number from a text string

1. Extracting number from a text string

Hi! Already found a partial solution to my problem from this board - I need to extract 8-digit number from a text string. Quite like discussed in another thread (unfortunately can't post a link "Finding numbers in text string").
Option provided there by daddylonglegs partially matches my needs:

``Please Login or Register  to view this content.``
However this formula processes the string starting from the back (from right to left), but I'd need it to work left-to-right. So from a string of "Data 10569228 AND 11112365 00671990000105729657 String continuing"
I'd need to grab "10569228", however this formula currently grabs "05729657"

Could you please help me out? I'm having a hard time understanding this one. If it was able to ignore non-8 digit numbers, even better, but that's less important. Thanks a lot!

2. Re: Extracting number from a text string

Does this help?

In B1
Formula:
`Please Login or Register  to view this content.`

 A B 1 "Data 10569228 AND 11112365 00671990000105729657 String continuing" 10569228

3. Re: Extracting number from a text string

Or with string in A2
Formula:
`Please Login or Register  to view this content.`

4. Re: Extracting number from a text string

What does this mean?

If it was able to ignore non-8 digit numbers, even better, ...
The likelihood of us being able to crack this for you based on just one example of a text string you need to process is low. If neither of the solutions offered work for you, please provide a sample workbook with at least 10-15 rows of data for us to work with, showing the variations we might see across your entire dataset.

5. Re: Extracting number from a text string

deleted message

6. Re: Extracting number from a text string

Originally Posted by FlameRetired
Does this help?

In B1
Formula:
`Please Login or Register  to view this content.`

 A B 1 "Data 10569228 AND 11112365 00671990000105729657 String continuing" 10569228
Thanks but unfortunately not. It does work with the example I sent; however this 8-digit number can be located in any position within the string. For an example this code produces result NO from string INVOICE NO 10572296 ThisCompany LTD. Meanwhile I'm looking for 10572296

The code in my OP would work fine with this string; however if the string was INVOICE NO 10572296 AND 10572297 ThisCompany LTD; it would grab 10572297 instead of 10572296

7. Re: Extracting number from a text string

Originally Posted by FlameRetired
Or with string in A2
Formula:
`Please Login or Register  to view this content.`
Similar issues with this one. VOICE NO is grabbed from INVOICE NO 10572296 ThisCompany LTD

8. Re: Extracting number from a text string

Try this:

``Please Login or Register  to view this content.``

9. Re: Extracting number from a text string

Thanks a lot. Looks to be working just as I needed!

A funny thing - I mistakenly copied just a part of the formula at first to try it out with my data and looks to be working just as well:
``Please Login or Register  to view this content.``

10. Re: Extracting number from a text string

I think that must be a fluke - are you SURE that thiis all there is in the cell? Expand the formula bar to check - maybe the rest of the formula is split over several lines. It really should not work as you have shown it - it should throw a syntax error.

11. Re: Extracting number from a text string

AliGW - you are right, it doesn't work like that. I must have seen an awkwardly cut snippet of formula on my formula bar - should have dragged the formula box bigger to be sure. Sorry, will mark the thread as solved - I'm new to the board, didn't notice that.

There are currently 1 users browsing this thread. (0 members and 1 guests)