How to extract numbers alphanumeric text

[Lingks]

How to extract numbers like 123456-A only.

from …. to ….

775506-V 775506
206981-D 206981
545077-A 545077
372194-M 372194
971720-K 971720
81500-D 81500
1089523-K 1089523
648330-T 648330
794417-M 794417
950894-T 950894
21731-K 21731
1129538-D 1129538
409627-D 409627
LL10413 10413
456668-U 456668

 

[Chihiro]

Something like below (taken from https://excelxor.com/category/extracting-numbers-from-a-string/). You will find it one of best site for this sort of thing.

=-LOOKUP(1,-(MID(A3,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A3&1/17)),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A3))))&"**0"))

 

[bosco_yip]

Another option :

=-LOOKUP(1,-MID(A3,AGGREGATE(15,6,FIND({0,1,2,3,4,5,6,7,8,9},A3),1),ROW($1:$15)))

Regards

 

[bobhc]

http://chandoo.org/forum/threads/extract-from-a-string-of-text.23516/

 

[Lingks]

Thank you all, it is solved now.

 

[Lingks]

Hi, while i am doing the testing just now. i add on one scenario like 0123456-a the formula does not show zero in front, how can i solve this?

The outcome should be 0123456.

 

[raviprakash]

Hi,
Use the below link and download the excel which contains code in it.
Just use =ExtractNums() User Defined function to get the result.

https://newtonexcelbach.wordpress.com/2017/03/11/extracting-numbers-from-text-update/

Regards,
Ravi

 

[bosco_yip]

Hi, while i am doing the testing just now. i add on one scenario like 0123456-a the formula does not show zero in front, how can i solve this? The outcome should be 0123456.

Hi,

1] In A3, array formula (confirm pressing CTRL+SHIFT+ENTER 3 keystrokes together) copy down :

=MID(A3,MIN(FIND(ROW($1:$10)-1,A3&1/19)),COUNT(-MID(A3,ROW($1:$99),1)))

2] See attachment.

Regards
Bosco

 

[shrivallabha]

Just for the kicks, following should work (non-CSE). Bosco's formula is better from maintainability point of view.
=MID(A3,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A3&"0123456789",1)),(LOOKUP(2,1/ISNUMBER(--MID(A3,ROW($A$1:INDEX($A:$A,LEN(A3))),1)),ROW($A$1:INDEX($A:$A,LEN(A3))))+1)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A3&"0123456789",1)))

 

[bosco_yip]

Another non-CSE formula in similar concept of Shrivallabha's, just using MATCH instead of LOOKUP.

=MID(A3,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A3&1/19)),MATCH(1,INDEX(-MID(A3,ROW($1:$99),1),0))-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A3&1/19))+1)

Regards
Bosco

 

[Lingks]

what is the different between CSE and non-CSE formula? what does maintainability refer to?

I search on google on this array formula but don't quite get it.
https://support.office.com/en-us/ar...formulas-7D94A64E-3FF3-4686-9372-ECFD5CAA57C7

mind to explain in layman term?

many thanks in advance

 

[shrivallabha]

A] CSE means CTRL+SHIFT+ENTER which is required for committing Array formulas. Array formulas are powerful way of achieving / working out results in much the similar ways as non-CSE formulas do.

In normal scenario, an array formula may not be a big overhead but the crux is to remember to commit formula by using CSE strokes simultaneously. If you enter formula normally it will return some error or incorrect results or a rare correct result for a case which will become incorrect as soon as parameters change etc. So precisely from that perspective, a normal user may get wrong results due to no knowledge of CSE entry.

B] The formula posted by Bosco though CSE, is simpler to read and interpret (if compared with the formula I've posted later) and therefore easier to implement in other situations e.g. changes in layouts etc. So that it is better from maintainability viewpoint.

Hope this explains. With this in mind, you should be able to decide which formula will suit you better!