# Solved Extract only certain digits from a string of numbers

July 24, 2015 at 12:41:33
Specs: Windows 7
 I need to extract the 7-11th digits from a string of numbers into another cell.e.g.- A1 "617018646085" I want "64608" in B1

See More: Extract only certain digits from a string of numbers

#1
July 24, 2015 at 13:04:23
 You will need to use the =MID() function:=MID(A1,7,5)MIKEhttp://www.skeptic.com/

Report •

#2
July 24, 2015 at 13:32:05
 That's great!!! Thank you so much!

Report •

#3
July 24, 2015 at 16:18:10
 Just a quick caveat, the =MID() function is a text string handling function.What this means, if you haven't noticed,is that the number it returns, is not formatted as a number but is seen by Excel as Text.You can determine this because the number is Left justified in the cell, where as numbers are always right justified in the cell.Excluding, of course, any formatting you may have placed on the cell yourself.If you want to return a number, then try this:=MID(A1,7,5)*1Multiplying by One forces it to convert to a real number.MIKEmessage edited by mmcconaghy

Report •

Related Solutions

#4
July 24, 2015 at 17:29:42
 ` `=RIGHT(LEFT(A1,(LEN(A1)-1)),5)*1, of course.;-)

Report •

#5
July 25, 2015 at 08:10:32
 I think the word "convoluted" fits......perhaps "intricate", but certainly not "simple" :-)MIKEhttp://www.skeptic.com/

Report •

#6
July 25, 2015 at 18:47:27
 Oh...you wanted simple! Sorry, my mistake.Here you go, and you don't even need the *1.=LOOKUP(99^99,--("0"&MID(SUBSTITUTE(A1,MID(A1,12,1),REPT(" ",1)),MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},SUBSTITUTE(A1,MID(A1,1,6),REPT(" ",6))&"0123456789")),ROW(1:10000))))

Report •

#7
July 25, 2015 at 20:22:24
 Now that's a FORMULA !!!!!!!!MIKEhttp://www.skeptic.com/

Report •

#8
July 26, 2015 at 16:42:44
 The core formula is something I found on the ole' interweb for extracting a number from a text string. I won't claim to completely understand it (feel free to figure it out for us, if you want) but what is does is extract the first series of digits it finds between 2 letters or before the first letter if the string starts with a number. In other words...a123b45678 --> 1235678z1223 --> 5678=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(\$1:\$10000))))Since the example data in the OP doesn't include any "text", I forced some spaces into the value in A1 with: SUBSTITUTE(A1,MID(A1,12,1),REPT(" ",1) andSUBSTITUTE(A1,MID(A1,1,6),REPT(" ",6)As I said, I don't completely understand how the core formula works, so I just used some "intelligent guessing" until I came up with a formula that returns the "7-11th digits from a string of numbers" as requested.

Report •