Results 1 to 13 of 13
Thread: Extract Middle Part from Numeric

20140122, 06:46 #1
 Join Date
 Oct 2013
 Posts
 23
 Thanks
 3
 Thanked 0 Times in 0 Posts
Extract Middle Part from Numeric
Hello,
I have a situation were i want to extract the 3 digits from a 9 digits field which is located in centre.
For example: In column 'B' 110121003, 201322789, 576914857 i want this 121, 322, 914 in Column 'A'
Is it possible.
Thanks in advance.

20140122, 07:18 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Something like
=MID(B1,4,3)
and copy it down the column should do it whether it is a number or a string.
Steve
PS If you want the result to be a number, use:
=VALUE(MID(B1,4,3))

20140122, 18:18 #3
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,470
 Thanks
 30
 Thanked 62 Times in 58 Posts
Performance question for you, Steve.
Is VALUE (per your solution) faster than doing, say, =0+MID(B1,4,3) or =1*MID(B1,4,3) or =  MID(B1,4,3) ?

20140122, 18:36 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Not sure it really matters performance wise from a practical standpoint. I have never checked (I imagine it would take thousands, if not hundreds of thousands, calculations to even detect a difference. If I were to speculate I would guess (timewise): value < 0+ ~ 1* <<  .
[My logic is that Value is a built in function set to do this conversion. I suspect the math processes must first use "value" to convert the text before it can work. Addn to me is "standard", multiplication is more complex routine than addn and  [=(1)*(1)*] is essentially 2 multiplications so should be double the processing of 1*, but I am just guessing]
In any of the cases the MID function will probably be much slower process than even the .
Steve

20140123, 04:54 #5
 Join Date
 Oct 2013
 Posts
 23
 Thanks
 3
 Thanked 0 Times in 0 Posts
Hi,
Thanks to all for your valuable answers, but now i face a new difficulty if suppose the mid part contains a zero then that gets trimmed in col A.
if its 001 then column A has value of only 1, if its 011 then column A has 11. I want to retain that leading zero's also.
Please suggest.

20140123, 05:46 #6
 Join Date
 Dec 2009
 Location
 Earth
 Posts
 8,180
 Thanks
 47
 Thanked 983 Times in 913 Posts
Then lose VALUE and use TEXT.
[edit] Just lose VALUE works for me.
cheers, PaulLast edited by Paul T; 20140123 at 05:50.

20140123, 05:50 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
If you want it to be text do not use the VALUE. If you want it as a number but to keep leading zeroes you can use a custom number format as (no quotes): "000"
Steve

20140123, 08:22 #8
 Join Date
 Oct 2013
 Posts
 23
 Thanks
 3
 Thanked 0 Times in 0 Posts
Hi,
Thanks a lot.
=MID(B1,4,3)
Done.

20140205, 00:05 #9
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,635
 Thanks
 115
 Thanked 649 Times in 592 Posts
Just for fun, a mathematical approach as a UDF?
Code:Public Function Middle3(num As Double) Middle3 = WorksheetFunction.RoundDown(((num / 10 ^ 6)  WorksheetFunction.RoundDown(num / 10 ^ 6, 0)) * 1000, 0) End Function
Maud

20140205, 07:34 #10
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,635
 Thanks
 115
 Thanked 649 Times in 592 Posts
Although there is a simpler way of doing this as described in the previous posts, playing with this formula uncovered some interesting properties. Changing the power of 10 and the number of zeros in the multiplication at the end allows manipulation of which values are extracted.
ExtractNums.png
To make it more versatile, parameters for the starting point and length were added to the UDF. Given the above solution, I don't know if there would ever be a need but if so, the calling cell formula would be:
=middle(num, start, len) e.g. =middle(A1,6,3)
Code:Public Function Middle(num As Double, start As Integer, length As Integer) As Integer Application.Volatile If num > 10 ^ (start  1) Then Middle = WorksheetFunction.RoundDown(((num / 10 ^ start)  WorksheetFunction.RoundDown(num / 10 ^ start, 0)) * 10 ^ length, 0) End If End Function

20140205, 08:50 #11
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
I don't see the need for a UDF, they are much slower than normal excel functions and force teh possible macro warnings. A numeric approach could be done with a normal formula:
=ROUND(MOD(B1,1000000)/1000,0)
But I think that:
=VALUE(MID(B1,4,3))
is more intuitive and without the value, is a more general method.
Steve

20140205, 15:30 #12
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,635
 Thanks
 115
 Thanked 649 Times in 592 Posts
Just for fun, a mathematical approach as a UDF.....Last edited by Maudibe; 20140205 at 15:34.

20140217, 08:04 #13
 Join Date
 Feb 2014
 Posts
 6
 Thanks
 0
 Thanked 0 Times in 0 Posts
You know, i had no idea you could do that in excel