How to extract just numbers if i know the start point but not the end in a text string.

[DeanRobinson]

<TABLE style="WIDTH: 167pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=223><COLGROUP><COL style="WIDTH: 167pt; mso-width-source: userset; mso-width-alt: 8155" width=223><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 167pt; HEIGHT: 15.75pt; BORDER-TOP: black 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl66 height=21 width=223>NK100/SSERRE/9108300D</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 15.75pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 height=21>NK100/SSERRE/141070</TD></TR><FORM id=aspnetForm method=post name=aspnetForm action=http://office.microsoft.com/en-us/excel-help/extracting-numbers-from-alphanumeric-strings-HA001154901.aspx></FORM><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 15.75pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 height=21>NK100/SSERRE/4831919SW2</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 15.75pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 height=21>NK100/SSERRE/5146118D</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 15.75pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 height=21>NK100/SSERRE/5146679D</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 15.75pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 height=21>NK100/SSERRE/4981999E</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 15.75pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 height=21>NK100/SSERRE/9494798D</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 15.75pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 height=21>NK100/SSERRE/5058204D</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 15.75pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 height=21>NK100/SSERRE/5058204D</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 15.75pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 height=21>NK100/SSERRE/143390</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 15.75pt; BORDER-TOP: black; BORDER-RIGHT: black 0.5pt solid" class=xl66 height=21>NK100/SSERRE/143494</TD></TR></TBODY></TABLE>

Above is a example of what i mean, i want to extract the number only after the / is know that first its a / and second its always at char 13 but i dont always know how long the number is and there could also be number after a letter at the end that cant be counted,

So it needs to be some thing like =mid(a1,13,find(istext) but im not sure on the syntax on the last bit also it might not find any text after the char 13? any help would be greatly appricated.

 

[Smitty]

If you know that you'll always have 13 leading characters then this should do it:

=RIGHT(A1,LEN(A1)-13)

HTH,

 

[Rick Rothstein]

Assuming the text in the cells in Column A will never be longer than 99 characters...

=LOOKUP(9.9E+307,1*LEFT(MID(A1,14,99),ROW($1:$99)))

Note: This formula returns a real numbers meaning if any of your numbers after the slash have leading zeroes, those leading zeroes will be removed.

 

[Rick Rothstein]

If you know that you'll always have 13 leading characters then this should do it:

=RIGHT(A1,LEN(A1)-13)

What about the trailing letters and letter/numbers (such as the D and SW2)?

 

[Smitty]

What about the trailing letters and letter/numbers (such as the D and SW2)?

Ack! You're completely right. Maybe I should go get some sleep...I've been up for way too long. Thanks for catching that!

 

[Ron Coderre]

With
A1 containing a string with a / in the 13th position, followed by numeric characters...and possibly followed by more text.
example:
A1: NK100/SSERRE/9108300D

If you want to return the numeric value that follow the 13th character,
try this regular formula:
B1: =LOOKUP(10^99,--MID(A1,14,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}))

In the above example, the formula returns: 9108300

Is that something you can work with

 

[DeanRobinson]

Rick that works perfectly except there is the possibility of leading zeros?? is there any way of getting round that?

 

[DeanRobinson]

Ron again yours would work perfectly except for these leading zeros, ive tried it and theres only 2 that start with 0 out of 8000 cells, but as this evolves i dont know how many more will start with the 0

 

[DeanRobinson]

LOOKUP(10^99,--MID(A1,14,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}))

Ron sorry to be a pain but could you explain this formula, im sorry im quite new to this, what does the lookup(10^99,-- do?

 

[Rick Rothstein]

Rick that works perfectly except there is the possibility of leading zeros?? is there any way of getting round that?

First off, I posted that formula because you said "i want to extract the number only" and real numbers do not hold leading zeroes. With that said, I'm giving you this working formula, but please come back and see if there are other later submissions because I just know there must be a simpler formula than this...

=LEFT(REPT("0",99),FIND(LOOKUP(9.9E+307,--LEFT(MID(A1,14,99),ROW($1:$99))),A1)-14)&LOOKUP(9.9E+307,--LEFT(MID(A1,14,99),ROW($1:$99)))