Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 17

Thread: Convert Alphanumerics To Metric

  1. #1
    Join Date
    23rd July 2005
    Posts
    184

    Convert Alphanumerics To Metric

    I have a spreadsheet,section attached.
    In column a is the distance in miles and furlongs and yards..sometimes there is only miles..sometimes only miles and furlongs,,sometimes only miles and yards..

    I need a formula i can fill down in column b to convert to furlongs in decimal format
    e.g. 2miles 3 furlongs and 20 yards converts to 19.09 furlongs..

    there are 8 furlongs in a mile and 220 yards in a furlong...
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,099

    Re: Convert To Metric

    Your source cells are not numbers, but text. Anytime you combine non-numeric characters with numeric Excel views the resulting string as text.

    Can't you set up your worksheet with three columns, one each for miles, yards and furlongs, and enter true numeric values?

    Then you would only need this simple formula (based on the first row of values in your workbook) set up as per above. I've used cells C2:E2 to hold those values.
    =(C2*8)+(D2/220)+E2
    Note: the brackets in this formula are used only to help you understand the order of operations in the formula, but are not actually necessary in this particular formula. See order of operations in the Excel help file. Multiplication and division are always performed before addition or subtraction.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

  3. #3
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,698

    Re: Convert Alphanumerics To Metric

    Agree, the numbers need to remain numeric. It would take longer to write a UDF than it would for you to set you distances up numerically.

  4. #4
    Join Date
    23rd April 2007
    Posts
    3,340

    Re: Convert Alphanumerics To Metric

    You also might want to look at the CONVERT function.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    23rd July 2005
    Posts
    184

    Re: Convert Alphanumerics To Metric

    Thanks for your replies

    The problem is there are 200000 rows in the sheet so manually converting col a first to numbers is not on.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,698

  7. #7
    Join Date
    23rd July 2005
    Posts
    184

    Re: Convert Alphanumerics To Metric

    There is 15 years or so of data..I am using excel 2007..I just saved a section in the earlier format to get a solution..I can always just do smaller chunks of data in excel 2002 if necessary..

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    26th September 2008
    Posts
    19

    Re: Convert Alphanumerics To Metric

    I use a Dutch version of Excel and I'm not familiar enough with the translations of formulae, so I can't properly explain what I did, but I think this should do the trick.
    That is, the trick of breaking the values up in chunks of miles, furlongs and yards. With that out of the way, the formula provided by AAE should do the rest.

    If someone could post back with the formulae in an English Excel, it would help to have the solution also in the thread, instead of only in the attachment.

    The formulae break the text in column A down to three separate columns, for miles, furlongs and yards. The letters m, f and y in cells E1, F1 and G1 are necessary, they represent the values searched for in the values in column A.

    If a value doen't exist, it will go #VALUE! I hope you can work around that.
    From your post I gathered that furlongs are always one-digit, is that correct?

    Downside #1: if Column A only has a value of yards (e.g. 80y), it doesn't work.
    I'm sort of hoping you don't have values that only contain yards.

    Hope it helps, good luck.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    15th March 2007
    Location
    LONDON, UK
    Posts
    1,376

    Re: Convert Alphanumerics To Metric

    Here's a UDF fit for your purpose....

    VB:
    Option Explicit 
     
    Const MILES As Integer = 8 ' 8 Furlongs to a mile
    Const FURLONGS As Integer = 1 ' 1 Furlong to a Furlong (Duh!)
    Const YARDS As Integer = 220 ' 220 Yards to a Furlong
    Const MILE_FLAG As String = "m" 
    Const FURLONG_FLAG As String = "f" 
    Const YARD_FLAG As String = "y" 
     
    Public Function convertToFurlongs(ByVal rngCell As Range) As Double 
         
        Dim intMiles As Integer: Dim intFurlongs As Integer: Dim intYards As Integer 
        Dim strTemp As String 
         
         ' Made the function Volatile to all spreadsheet changes
        Application.Volatile 
         
         ' Assign a string variable to the range value.
        strTemp = Trim(rngCell.Value) 
         
         ' Check if there are any Mile flags in the range
        If InStr(1, strTemp, MILE_FLAG, vbTextCompare) <> 0 Then 
             ' If there is a mile flag in the range then get the number of miles
            intMiles = getNumericValues(CInt(InStr(1, strTemp, MILE_FLAG, vbTextCompare) - 1), strTemp) 
        Else 
             ' set the miles to zero if no mile flag found
            intMiles = 0 
        End If 
         
         ' Check if there are any Furlong flags in the range
        If InStr(1, strTemp, FURLONG_FLAG, vbTextCompare) <> 0 Then 
             ' If there is a furlong flag in the range then get the number of furlongs
            intFurlongs = getNumericValues(CInt(InStr(1, strTemp, FURLONG_FLAG, vbTextCompare) - 1), strTemp) 
             
        Else 
             ' set the furlong to zero if no furlong flag found
            intFurlongs = 0 
        End If 
         
         ' Check if there are any yard flags in the range
        If InStr(1, strTemp, YARD_FLAG, vbTextCompare) <> 0 Then 
             ' If there is a yard flag in the range then get the number of yards
            intYards = getNumericValues(CInt(InStr(1, strTemp, YARD_FLAG, vbTextCompare) - 1), strTemp) 
             
        Else 
             ' set the yard to zero if no yard flag found
            intYards = 0 
        End If 
         
         ' Return the calculation to the calling function
        convertToFurlongs = (intMiles * MILES) + (intFurlongs / FURLONGS) + (intYards / YARDS) 
         
    End Function 
     
    Private Function getNumericValues(ByVal int_position As Integer, _ 
        ByVal str_rng_value As String) As Integer 
         
         ' Temporary string to build the numeric value
        Dim strNumericValues As String 
         
        Do While int_position > 0 
             
            Select Case VBA.Mid(str_rng_value, int_position, 1) 
                 
                 ' If the current string position is a numeric value then add it to the
                 ' numeric values string
            Case 0 To 9 
                 
                strNumericValues = strNumericValues + VBA.Mid(str_rng_value, int_position, 1) 
                 
                 ' If the string is not a numeric value then exit the loop
            Case Else 
                Exit Do 
                 
            End Select 
             
            int_position = int_position - 1 
             
        Loop 
         
         ' Cast the string value to an integer and return to calling procedure
        getNumericValues = CInt(StrReverse(strNumericValues)) 
         
    End Function 
    
    
    HTH

    Craig
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    23rd July 2005
    Posts
    184

    Re: Convert Alphanumerics To Metric

    Thanks Marcel79 and Craig..looks as if both will work..there are no yards only...
    will have a closer look after work..

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Summary Count Of Alphanumerics
    By gjiale in forum EXCEL HELP
    Replies: 4
    Last Post: August 13th, 2008, 15:28
  2. Maximum Of Text & Numbers - Alphanumerics
    By ExcelUser777 in forum EXCEL HELP
    Replies: 2
    Last Post: July 31st, 2008, 13:35
  3. Increment Number & Text Of Alphanumerics
    By CLUBDEVEAU in forum EXCEL HELP
    Replies: 4
    Last Post: February 28th, 2008, 09:32
  4. Lookup Exact Alphanumerics
    By mattee6 in forum EXCEL HELP
    Replies: 3
    Last Post: December 16th, 2007, 09:37
  5. Extract Alphanumerics From String
    By EROEI in forum EXCEL HELP
    Replies: 15
    Last Post: July 30th, 2007, 13:58

Bookmarks

Posting Permissions

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