extract numeric field from alpha numeric text field

[navinkedia]

Please note the following

Data

100 1433463916-yrda sfh-dfh 123 ehs

I want to extract the numeric field between from the first blank to the first symbol "-" i.e. 1433463916

Thanks in advance to all the topguns here.

Regards,
Naveen

 

[fairwinds]

Hi

Try:

=MID(A1,SEARCH(" ",A1)+1,SEARCH("-",A1)-SEARCH(" ",A1)-1)

 

[navinkedia]

Thanks that works fine.

Regards,

 

[markhib]

Hi, I am trying to use fairwinds formula above on the following data..

Installed: 9059 Ser: F8R8WF1292134423|Installed: 9143v2 Ser: RK041C6009402|

Installed: TT00057 Qty: 1|Installed: 965 Ser: 21530317647S27033223|Installed: 9059 Ser: F8R8WF1292121846|Installed: 9143v2 Ser: RK041C7008389|

Installed: 9059 Ser: F8R8WF1290810473| I'm trying to extract the number that follows "Installed: 9059 Ser: " in each instance. Any advice? Many Thanks

<colgroup><col></colgroup><tbody>
</tbody>

 

[drowneddragon]

Hi, I am trying to use fairwinds formula above on the following data..

Installed: 9059 Ser: F8R8WF1292134423|Installed: 9143v2 Ser: RK041C6009402|

Installed: TT00057 Qty: 1|Installed: 965 Ser: 21530317647S27033223|Installed: 9059 Ser: F8R8WF1292121846|Installed: 9143v2 Ser: RK041C7008389|

Installed: 9059 Ser: F8R8WF1290810473|I'm trying to extract the number that follows "Installed: 9059 Ser: " in each instance. Any advice? Many Thanks

<tbody>
</tbody>

Installed: 9059 Ser: F8R8WF1290810473 I'm trying to extract the number that follows "Installed: 9059 Ser: " in each instance. Any advice?

Are you trying to extract "Installed: 9059 Ser:" part? If that's the case, do the numbers go higher than 4 digits?

 

[markhib]

Installed: 9059 Ser: F8R8WF1290810473 I'm trying to extract the number that follows "Installed: 9059 Ser: " in each instance. Any advice?

Are you trying to extract "Installed: 9059 Ser:" part? If that's the case, do the numbers go higher than 4 digits?

Hi,

No, i am trying to extract the following 16 digit alphanumeric code, thing is more than 1 product can be in each line, i just want the products that are 9059's.

Cheers

 

[drowneddragon]

Hi Marknib,

Oh.. is the the whole "9059 Ser: F8R8WF1290810473" part in one cell? And, you are trying to pull the last 16 digits? And, most importantly, are the consistent in 16 digits for the last part?
If that's the case, apply the following formula.

=right(referencing cell,16)

Once completed in that cell, just drag it down and it should do the job.

However, if you see inconsistent result (part number being cut off etc..) you may have a few extra spaces in the beginning or end or that referencing cell. If that's the case, enter =trim(referencing cell) upon inserting an extra column just right of the referencing column, which should eliminate all the unwanted spaces. Then, in insert another column next to it and apply the "=right" formula mentioned above.

That should do it.

 

[markhib]

Hi drowneddragon, thanks for the help. The data in each cell is as follows... Installed: 9059 Ser: F8R8WF1292134423|Installed: 9143v2 Ser: RK041C6009402|

Installed: TT00057 Qty: 1|Installed: 965 Ser: 21530317647S27033223|Installed: 9059 Ser: F8R8WF1292121846|Installed: 9143v2 Ser: RK041C7008389|

Installed: 9059 Ser: F8R8WF1290810473|" in each cell there will be "Installed: 9059 Ser:" followed by a 16 digit reference number. It is this reference number that i need to extract. In each cell, the format will not be the same, the "Installed: 9059 Ser:" will be at different places within the cell information, hence =right(A1,16) will not work. This is why i figured fairwinds logic was good, if i can pull out the 16 characters after the Installed: 9059 Ser: then i would be gold. Cheers

<tbody>
</tbody>

 

[ukmikeb]

Hi

Try :-

=MID(A1,FIND("Installed: 9059 Ser: ",A1)+21,16)

hth