# Thread: Extract Middle Part from Numeric

1. ## 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.

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

3. 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) ?

4. 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 (time-wise): 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

5. 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.

6. Then lose VALUE and use TEXT.
 Just lose VALUE works for me.

cheers, Paul

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

8. Hi,

Thanks a lot.
=MID(B1,4,3)

Done.

9. 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```
middle3.png

Maud

10. 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```

11. 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

12. Just for fun, a mathematical approach as a UDF.....
I think that kind of sums it up....

13. You know, i had no idea you could do that in excel

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•