Extracting a sequence of numbers within a string

[NewOrderFac33]

Good afternoon,

I have a series of variable length strings in an Excel list that look a bit like this:

promote Trans.no: 1173331 by bobsmith

I want to be able to extract the numeric element of the string which is usually, but not always eight characters

I know however that the number will always be preceded by "Trans.no: " and always followed by " by " as in the above example.

Any suggestions?

As always, thanks in advance

Pete

 

[RoryA]

Yes although if you have more than 15 digits of precision in your numbers, the additional numbers will change to 0.

 

[István Hirsch]

This formula finds "by" only after "Trans.no":

=MID(A1,FIND("Trans.no: ",A1)+10,FIND(" by ",A1,FIND("Trans.no: ",A1))-FIND("Trans.no: ",A1)-10)

 

[Rick Rothstein]

Rory,

Thank you - yes, that works too, although I understand it even less than I did Rick's..!

I assume that I adjust the 15s to whatever the maximum length of my numerical sequence is likely to be?

As Rory points out in Message #11, there is a limit to the precision of the number his formula will return (my formula has no such limit), but your post does not seem to indicate you will be approaching that limit; however, I did want to note for you, in case it could matter, that Rory's formula will not preserve leading zeroes for numbers having one or more zeroes at the beginning. I would also point out that Rory's formula is Volatile (my formula is not) which could matter if you plan to apply the formula to a lot of cells. We can modify Rory's formula to make it non-Volatile though (at the cost of adding two more function calls) like this...

=-LOOKUP(2,-LEFT(MID(A2,FIND("Trans.no: ",A2)+10,15),ROW(INDEX(A:A,1):INDEX(A:A,15))))

 

[NewOrderFac33]

Rick, Rory, Istvan,

Thanks for all your assistance. I am now able to successfully extract the data I require.

More usefully, I understand how each of the suggestions that you have provide work!

Pete

 

[XOR LX]

=NPV(-0.9,,IFERROR(MID(A2,1+LEN(A2)-ROW(INDIRECT("1:"&LEN(A2))),1)%,""))

Strange indeed, but the most original solution I've seen in a long time. Wonderful!!

Thanks for sharing!

Cheers

 

[RoryA]

I expect to see NPV appearing in a shortest formula challenge in the near future.

 

[Marcelo Branco]

Strange indeed, but the most original solution I've seen in a long time. Wonderful!!

I expect to see NPV appearing in a shortest formula challenge in the near future.

Yes, a very original and clever formula!

But I'll tell you a secret (do not tell anyone )

Despite knowing this formula for sometime now, every time I use it I can not fully understand how it works

M.

 

[XOR LX]

I expect to see NPV appearing in a shortest formula challenge in the near future.

 

[XOR LX]

Yes, a very original and clever formula!

But I'll tell you a secret (do not tell anyone )

Despite knowing this formula for sometime now, every time I use it I can not fully understand how it works

M.

Yes. I've tended to avoid all "financial" formulas in Excel until now, mainly because I don't have much understanding of that field.

Perhaps it's time to start looking into them though, especially if they can produce "non-financial" solutions such as this!!

Cheers

 

[István Hirsch]

Originally Posted by Marcelo Branco
=NPV(-0.9,,IFERROR(MID(A2,1+LEN(A2)-ROW(INDIRECT("1:"&LEN(A2))),1)%,""))



Strange indeed, but the most original solution I've seen in a long time. Wonderful!!

Thanks for sharing!

Cheers

When I first met the problem of extracting any number of digits from a mixed alphanumeric string with a formula in excel and tried to find a general solution (with no success), I wondered who had invented this brilliant solution (an accountant, a bank officer?) and could trace this use of NPV back to 2008 by Google.