get specific ID in cells

[reza_doang]

Hi All,

need your assistance on creating formula to get specific number in cells.
Currently i have many rows,
please see sample below, each id has 11 characters and can be start with 10000 or 20000


[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Description[/TD]
[TD]ID (expected result)[/TD]
[/TR]
[TR]
[TD]please use this id 10000428271 to fix this issue[/TD]
[TD]10000428271[/TD]
[/TR]
[TR]
[TD]id 10000428679 created[/TD]
[TD]10000428679[/TD]
[/TR]
[TR]
[TD]20000430091 raised
[/TD]
[TD]20000430091[/TD]
[/TR]
[TR]
[TD]xxxxx xxxxxx xxxxxxx 20000430096 xxxxxxxx xxxxxxxxxx[/TD]
[TD]20000430096[/TD]
[/TR]
[TR]
[TD]xxxxx xxxxxx xxxxxxx 200004550092[/TD]
[TD]20000450092[/TD]
[/TR]
</tbody>[/TABLE]













Thank you

 

[alpadem]

{=MID(A2,MATCH(TRUE,ISNUMBER(1*MID(A2,ROW($1:$100),1)),0),COUNT(1*MID(A2,ROW($1:$100),1)))}

NOTE: It is an array formula so do not forget { } or ALT+CTRL+SHIFT

 

[Rick Rothstein]

Here is another (normally entered) formula that I think should do what you want...

=MID(A2,MIN(FIND(10000,A2&10000),FIND(20000,A2&20000)),11)

 

[reza_doang]

Thanks Alpadem, i tried but i was not work

Thanks Rick, it works but not perfect. In 1 cell if we have number another number which contains 20000, the formula will get that number.
i.e in 1 cell, have this words

please check the PO# 9200004735, ticket number 20000430096

if use your formula the result wil be "200004735- " not the ticket number.

thanks

 

[Rick Rothstein]

TThanks Rick, it works but not perfect. In 1 cell if we have number another number which contains 20000, the formula will get that number.
i.e in 1 cell, have this words

please check the PO# 9200004735, ticket number 20000430096

None of your examples in Message #1 indicated this kind of construction, so there was no way for me to know I would have to account for it. It is always best to post several (if necessary) fully representative examples of your data so we know what we have to work with as well as work around.

 

[reza_doang]

None of your examples in Message #1 indicated this kind of construction, so there was no way for me to know I would have to account for it. It is always best to post several (if necessary) fully representative examples of your data so we know what we have to work with as well as work around.

Hi Rick ,

Yes, i am really sorry about that. i just got that scenario when i tried using your formula.

 

[alpadem]

Thanks Alpadem, i tried but i was not work

Thanks Rick, it works but not perfect. In 1 cell if we have number another number which contains 20000, the formula will get that number.
i.e in 1 cell, have this words

please check the PO# 9200004735, ticket number 20000430096

if use your formula the result wil be "200004735- " not the ticket number.

thanks

Hi Reza

I tried again and works fine. You should write =MID(A2,MATCH(TRUE,ISNUMBER(1*MID(A2,ROW($1:$100),1)),0),COUNT(1*MID(A2,ROW($1:$100),1))) then before press enter you should CTRL+SHIFT+ENTER, sorry my bad i said my previous mail ALT+CTRL+SHIFT )

Even it works PO# 9200004735

 

[jtakw]

I tried again and works fine. You should write =MID(A2,MATCH(TRUE,ISNUMBER(1*MID(A2,ROW($1:$100),1)),0),COUNT(1*MID(A2,ROW($1:$100),1))) then before press enter you should CTRL+SHIFT+ENTER,
Even it works PO# 9200004735

Hi,

@ alpadem, maybe I tested your formula incorrectly, but returned the following incorrect result:

Book1
	A	E
7	please check the PO# 9200004735, ticket number 20000430096	9200004735, ticket nu
Sheet573

Cell Formulas
Range	Formula
E7	{=MID(A7,MATCH(TRUE,ISNUMBER(1*MID(A7,ROW($1:$100),1)),0),COUNT(1*MID(A7,ROW($1:$100),1)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

@OP, try this modified version of Rick's formula:

Book1
	A	B
1	Description	ID (expected result)
2	please use this id 10000428271 to fix this issue	10000428271
3	id 10000428679 created	10000428679
4	20000430091 raised	20000430091
5	xxxxx xxxxxx xxxxxxx 20000430096 xxxxxxxx xxxxxxxxxx	20000430096
6	xxxxx xxxxxx xxxxxxx 20000450092	20000450092
7	please check the PO# 9200004735, ticket number 20000430096	20000430096
Sheet573

Cell Formulas
Range	Formula
B2	=MID(A2,MIN(FIND(" "&10000," "&A2&" 10000"),FIND(" "&20000," "&A2&" 20000")),11)

 

[reza_doang]

Hi,

@ alpadem, maybe I tested your formula incorrectly, but returned the following incorrect result:

	A	E
please check the PO# 9200004735, ticket number 20000430096	9200004735, ticket nu

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]7[/TD]

</tbody>

Sheet573​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E7[/TH]
[TD="align: left"]{=MID(A7,MATCH(TRUE,ISNUMBER(1*MID(A7,ROW($1:$100),1)),0),COUNT(1*MID(A7,ROW($1:$100),1)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



@OP, try this modified version of Rick's formula:

	A	B
Description	ID (expected result)
please use this id 10000428271 to fix this issue	10000428271
id 10000428679 created	10000428679
20000430091 raised	20000430091
xxxxx xxxxxx xxxxxxx 20000430096 xxxxxxxx xxxxxxxxxx	20000430096
xxxxx xxxxxx xxxxxxx 20000450092	20000450092
please check the PO# 9200004735, ticket number 20000430096	20000430096

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

</tbody>

Sheet573​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=MID(A2,MIN(FIND(" "&10000," "&A2&" 10000"),FIND(" "&20000," "&A2&" 20000")),11)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you jtakw, really appreciate with your help

 

[jtakw]

Thank you jtakw, really appreciate with your help

You're welcome.

Rick did the work, I just modified his formula for your additional requirement.