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.

 

[DeanRobinson]

Sorry Rick my mistake, these are work order numbers and i thought they would never start with a zero until i filled your old formula down and found the N/A# but many thanks for the revision.

 

[Rick Rothstein]

Sorry Rick my mistake, these are work order numbers and i thought they would never start with a zero until i filled your old formula down and found the N/A# but many thanks for the revision.

There may be a shorter, more efficient formula available depending on the possible ways your work order numbers are constructed. The trailing text after the number you want... what are all the possible characters they can start with? Your sample showed "D", "E", and "S" (from the "SW2)... what other letters are possible and can there be non-letters (such as a punctuation mark or accented letters) after the sought after number? Remember, I am only interested in the first non-digit character after the sought after number, not any of the other text that might follow that first non-digit.

 

[circledchicken]

Hi,

Here's one alternative (entered with CTRL+SHIFT+ENTER):

=LEFT(RIGHT(A1,LEN(A1)-13),MATCH(FALSE,ISNUMBER(1*MID(RIGHT(A1,LEN(A1)-13),ROW($1:$100),1)),0)-1)

This is based on a very helpful explanation by Ashish Mathur here:
http://office.microsoft.com/en-us/e...rs-from-alphanumeric-strings-HA001154901.aspx

 

[Rick Rothstein]

Here is a shorter array-entered** formula that you can use...

=MID(A1,14,MIN(IF(NOT(ISNUMBER(--MID(A1,13+ROW($1:$99),1))),ROW($1:$99),9E+99))-1)

**commit this formula using CTRL+SHIFT+ENTER, not just Enter by itself

 

[DeanRobinson]

Sorry it took so long to reply, it got a little late last night. I think i should of said a bit more in my description as the formulas you guys have put have done exactly what i asked for the issues is i dont think i asked the right question,.

the formulas work and i now am using them for what i need except for one issue, these formulas store the result as text, then i have to add another column copy and paste special values before i can then vlookup the results, its not an issue but is there a way that last step the copy and paste can be skipped.

Also Rick to answer the question about what characters can follow the number, this could be anything.

What we have is a unique number that we use the work order number, which is the number that your formula pulled out, then all of the extra letters and numbers are added by the city council, which we have no control over what characters they use and how many they use.

 

[barry houdini]

Hello Dean,

If you use the formula proposed by Ron Coderre then that will provide a numeric answer.......but the problem you found with that was that it lost the leading zeroes....so you either have one problem or another.

I suggest you return the result as text using this formula in B1

=MID(A1,14,LOOKUP(0,-MID(A1,14,ROW($1:$99)),ROW($1:$99)))

then when you use that value in the VLOOKUP formula convert to a value in the formula, e.g. using a formula like

=VLOOKUP(B1+0,.....

 

[DeanRobinson]

Ahh ok, so the +0 is basically changing the value to a number inside the formula then.

 

[Rick Rothstein]

Ahh ok, so the +0 is basically changing the value to a number inside the formula then.

The "general rule" is if you involve a text string that looks like a number in a mathematical operation, Excel will convert the text string to its numerical value in order to be able to complete the mathematical operation. Adding 0 to your text string involves it in a mathematical operation and the addition of 0 is used so the overall calculation will not change the text string's numerical value once the conversion has occurred. Two other "standard" ways of doing this would be...

Multiply by one: 1*B1
Double unary: --B1

where the double minus sign is the equivalent of multiplying by minus one twice (-1 * -1 = +1).