# Extract Data Before Non-Numeric Character

#### BeeLev

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

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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

Last edited:
Hi,

Here's another one:

Book1
AB
1Tested PW3131
2Call re: PW67986798
3Reviewed info PW64167/264167
4Updated PW 1,2,411
Sheet508
Cell Formulas
RangeFormula
B1=-LOOKUP(1,-MID(A1,SEARCH("PW",A1)+2,ROW(\$1:\$99)+0))

@jtakw

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

This is why I included the part

&"**0"

Regards

@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)))

Last edited:
@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.

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

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

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!

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

Replies
1
Views
679
Replies
25
Views
1K
Replies
18
Views
1K
Replies
12
Views
740
Replies
5
Views
2K

1,202,976
Messages
6,052,880
Members
444,606
Latest member
rwmhr

### 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