Extract Data Before Non-Numeric Character

[BeeLev]

I'd like to extract the numeric values after "PW" but before any non-numeric values. I've tried EXTRACTNUMBERS(MID(F2,find("PW",F2)+2,6), but I'm picking up extraneous numbers.

Example:

Tested PW31
Call re: PW6798
Reviewed info PW64167/2
Updated PW 1,2,41

Desired Output:
31
678
64167
1

Thanks!

 

[XOR LX]

Hi,

Assuming the desired extraction can never contain more than 10 digits:

=-LOOKUP(1,-(MID(A1,FIND("PW",A1)+2,{1,2,3,4,5,6,7,8,9,10})&"**0"))

Regards

 

[jtakw]

Hi,

Here's another one:

Book1
	A	B
1	Tested PW31	31
2	Call re: PW6798	6798
3	Reviewed info PW64167/2	64167
4	Updated PW 1,2,41	1
Sheet508

Cell Formulas
Range	Formula
B1	=-LOOKUP(1,-MID(A1,SEARCH("PW",A1)+2,ROW($1:$99)+0))

 

[XOR LX]

@jtakw

For e.g. Updated PW1/2/12, that returns 40940 instead of 1.

This is why I included the part

&"**0"

Regards

 

[jtakw]

@XOR LX, I've seen you use "**0" on several different scenarios, do you mind explaining what the does, I'm genuinely interested to know. Thanks.

EDIT: don't know what happened to my post, had to repost this...

2nd EDIT, too late to edit my post # 3, don't need the +0 for my formula, can be just:

=-LOOKUP(1,-MID(A1,SEARCH("PW",A1)+2,ROW($1:$99)))

 

[jtakw]

@jtakw

For e.g. Updated PW1/2/12, that returns 40940 instead of 1.

This is why I included the part

&"**0"

Regards

Thanks XOR LX, one more question, why does that only work with 2 asterisks, not 1, not 3, etc.

 

[XOR LX]

Sure.

Catenation of the string "**0" ensures that a string such as "1/2/12" becomes "1/2/12**0" and so can no longer be interpreted as the (numeric) date 40940.

This works because "**0" is equivalent to "E0", i.e. represents scientific notation with an index of 0, and, since 10^0 is unity, we also guarantee that any numbers represented in this form will be unchanged, e.g. 0+(12&"**0") becomes 0+("12**0"), which is 12x10^0, i.e. 12.

Note that this choice of additional string is to be preferred over the equivalent "E0". For example, for the string:

Updated PW12 Jun

the formula

=-LOOKUP(1,-(MID(A1,FIND("PW",A1)+2,{1,2,3,4,5,6,7,8,9,10})&"**0"))

correctly returns 12, though

=-LOOKUP(1,-(MID(A1,FIND("PW",A1)+2,{1,2,3,4,5,6,7,8,9,10})&"E0"))

incorrectly returns 36689, since the resulting substring "12 – JunE0", by pure misfortune (“Jun” & “E” is still “June”!), happens to represent, when coerced to a numeric, the date 12/06/2000.

Also, as Lori points out, “the “E” might also be interpreted differently in different locales.

(Largely taken from https://excelxor.com/2015/06/29/simultaneous-locating-of-first-and-last-numbers-in-a-string/)

Regards

 

[jtakw]

Thanks XOR LX, for the explanation and the link, I've read some of your other articles before, Thanks again.

 

[XOR LX]

Any time!

Cheers

P.S See you're in Oakland. Spent a whole summer in San Jose and thereabouts many years back...nice part of the world!

 

[BeeLev]

Thanks for the quick responses!! The original solution worked great, except for some of my data has "PW" referenced multiple times and the output is a small deciml number.. Can the formula to be adjusted to only pick up the first "PW" reference?

Example: Created PW 3, Edited PW 3, Reviewed PW 3

Desired Output: 3