extract numeric field from alpha numeric text field

navinkedia

New Member
Joined
Dec 12, 2002
Messages
19
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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Hi

Try:

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

markhib

New Member
Joined
Apr 21, 2008
Messages
19
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

New Member
Joined
Aug 2, 2013
Messages
5

ADVERTISEMENT

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

New Member
Joined
Apr 21, 2008
Messages
19
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

New Member
Joined
Aug 2, 2013
Messages
5

ADVERTISEMENT

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.
 
Last edited:

markhib

New Member
Joined
Apr 21, 2008
Messages
19
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

Well-known Member
Joined
Jul 10, 2009
Messages
2,757
Hi

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

hth
 
Learn Excel from Bill Jelen

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

Forum statistics

Threads
1,151,491
Messages
5,764,675
Members
425,229
Latest member
Rashid mahmood

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top