Extract Middle Part from Numeric

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Then lose VALUE and use TEXT.
[edit] Just lose VALUE works for me.

cheers, Paul

Reply | Quote

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

Reply | Quote

Hi,

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

Done.

Reply | Quote

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

36167-middle3

Maud

Reply | Quote

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.

36168-ExtractNums

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

Reply | Quote

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

Reply | Quote

Just for fun, a mathematical approach as a UDF…..

I think that kind of sums it up….

Reply | Quote

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

Reply | Quote