# get specific ID in cells

#### reza_doang

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

 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 200004550092 20000450092

<tbody>
</tbody>

Thank you

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
{=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

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)

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

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.

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.

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

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
AE
7please check the PO# 9200004735, ticket number 200004300969200004735, ticket nu
Sheet573
Cell Formulas
RangeFormula
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
AB
1DescriptionID (expected result)
2please use this id 10000428271 to fix this issue10000428271
3id 10000428679 created10000428679
420000430091 raised20000430091
5xxxxx xxxxxx xxxxxxx 20000430096 xxxxxxxx xxxxxxxxxx20000430096
6xxxxx xxxxxx xxxxxxx 2000045009220000450092
7please check the PO# 9200004735, ticket number 2000043009620000430096
Sheet573
Cell Formulas
RangeFormula
B2=MID(A2,MIN(FIND(" "&10000," "&A2&" 10000"),FIND(" "&20000," "&A2&" 20000")),11)

Last edited:
Hi,

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

AE
7please check the PO# 9200004735, ticket number 200004300969200004735, ticket nu

</tbody>
Sheet573

Array Formulas
CellFormula
E7{=MID(A7,MATCH(TRUE,ISNUMBER(1*MID(A7,ROW(\$1:\$100),1)),0),COUNT(1*MID(A7,ROW(\$1:\$100),1)))}

</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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

AB
1DescriptionID (expected result)
2please use this id 10000428271 to fix this issue10000428271
3id 10000428679 created10000428679
420000430091 raised20000430091
5xxxxx xxxxxx xxxxxxx 20000430096 xxxxxxxx xxxxxxxxxx20000430096
6xxxxx xxxxxx xxxxxxx 2000045009220000450092
7please check the PO# 9200004735, ticket number 2000043009620000430096

</tbody>
Sheet573

Worksheet Formulas
CellFormula
B2=MID(A2,MIN(FIND(" "&10000," "&A2&" 10000"),FIND(" "&20000," "&A2&" 20000")),11)

</tbody>

<tbody>
</tbody>

Thank you jtakw, really appreciate with your help

Thank you jtakw, really appreciate with your help

You're welcome.

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

Replies
5
Views
1K
Replies
6
Views
933
Replies
5
Views
470
Replies
0
Views
419
Replies
4
Views
491

1,196,346
Messages
6,014,731
Members
441,843
Latest member
benji 71

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