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]

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

The formula in post #2 does pick up the first reference. For the above example it returns 3, as desired.

Not sure what you mean by "small deciml number"?

Regards

 

[jtakw]

@OP, so does my formula, don't understand the problem:

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
5	Created PW 3, Edited PW 3, Reviewed PW 3	3
6	Created PW 3, Edited PW 4, Reviewed PW 5	3
Sheet508

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

 

[BeeLev]

AHH! I had one misplaced parenthesis throwing off the formula, it works perfectly! thank you!!

 

[jtakw]

You're welcome, glad we can help.

 

[Rick Rothstein]

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.

What if you use "E+0" instead of "E0"?

 

[XOR LX]

What if you use "E+0" instead of "E0"?

Not sure, haven't tested! Have you?

Might get round one of the issues I mentioned. Any idea about the locale-dependency or not of "E" in that construction?

Regards

 

[PHIL.Pearce84]

Hi All,

This is exactly the sort of solution I need for my current problem.

Question how could you amend the formula to not find PW, but I know when the first numeric value starts? For example I know it starts at say character 18 (this can vary) and I just want to extract the numeric values up until the next non numeric?

Hope you can help.

Thanks

 

[RoryA]

You'd be better off starting your own thread rather than resurrecting a 5 year old one.

 

[PHIL.Pearce84]

You'd be better off starting your own thread rather than resurrecting a 5 year old one.

Thanks, will do that now