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

Report •


#1
July 24, 2015 at 13:04:23
✔ Best Answer
You will need to use the =MID() function:

=MID(A1,7,5)

MIKE

http://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)*1

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


MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

Related Solutions

#4
July 24, 2015 at 17:29:42
     

=RIGHT(LEFT(A1,(LEN(A1)-1)),5)

*1, of course.

;-)

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


Report •

#5
July 25, 2015 at 08:10:32
I think the word "convoluted" fits......
perhaps "intricate",
but certainly not "simple"

:-)

MIKE

http://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))))

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


Report •

#7
July 25, 2015 at 20:22:24
Now that's a FORMULA !!!!!!!!

MIKE

http://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 --> 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.


Report •


Ask Question