Results 1 to 13 of 13
  1. #1
    New Lounger
    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.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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))

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

  4. #4
    WS Lounge VIP sdckapr's Avatar
    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 (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. #5
    New Lounger
    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.

  6. #6
    WS Lounge VIP
    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, Paul
    Last edited by Paul T; 2014-01-23 at 05:50.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    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

  8. #8
    New Lounger
    Join Date
    Oct 2013
    Posts
    23
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Hi,

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

    Done.

  9. #9
    Gold Lounger Maudibe's Avatar
    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
    middle3.png

    Maud

  10. #10
    Gold Lounger Maudibe's Avatar
    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

  11. #11
    WS Lounge VIP sdckapr's Avatar
    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

  12. #12
    Gold Lounger Maudibe's Avatar
    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.....
    I think that kind of sums it up....
    Last edited by Maudibe; 2014-02-05 at 15:34.

  13. #13
    New Lounger
    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

Posting Permissions

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