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

That's great!!! Thank you so much!

Just a quick caveat,

the =MID() function is atext stringhandling function.What this means, if you haven't noticed,

is that the number it returns, is not formatted as anumber

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)*1

Multiplying by One forces it to convert to a real number.

MIKE

message edited by mmcconaghy

=RIGHT(LEFT(A1,(LEN(A1)-1)),5)*1, of course.

;-)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

I think the word "convoluted" fits......

perhaps "intricate",

but certainly not "simple":-)

MIKE

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))))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

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 --> 123

5678z1223 --> 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) and

SUBSTITUTE(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.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

Do you think Salesforce should have bought Slack?

Discuss in The Lounge

Poll History