# How to extract numbers from text with specified and unique conditions

#### Lil Hotpocket

##### New Member
<quote>
With A1:A7 containing
Code:
45t*&65/
9128A+BC37/E*465
91a28ABC3712DEF465
91.28ABC3712DEF465
91.28ABC37.1D2F465
464.59 DDFSDF 23.25 ccd 157.25
123asdf.asdf.asdf456

and
C1: 2

This array formula returns the specifiedNUMBER from the string,
Code:

B1: =LOOKUP(10^99,--MID("|"&A1,SMALL(IF(((--ISNUMBER(--("0"&MID(
SUBSTITUTE(" "&A1," ","|"),ROW(\$1:\$25),1)))=0)*ISNUMBER(--(MID(
SUBSTITUTE(" "&A1," ","|"),ROW(\$2:\$26),1)))),ROW(\$2:\$26)),C1),ROW(\$1:\$25)))

Copy B1 and paste into B2:B7

With the above examples, the formulas return these values:
Code:
45
37
28
91.28
2
23.25
456

NOTE If you want to display two decimal places, change the number format.

Are we done, yet?

I was curious if someone could tell me how to change this formula and add some properties. Such as: To ignore number strings lead by zeros, if an x is directly behind a + = - its counted as a 1 is extrated (or in the case of the - a -1), and if a - is directly in front of a number string, to extract that - along with the number string to make that number string negative.

</quote>

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Replies
1
Views
458
Replies
7
Views
1K
Replies
6
Views
353
Replies
10
Views
715
Replies
1
Views
517

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

1,151,589
Messages
5,765,314
Members
425,272
Latest member
Umba

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