Convert Alphanumerics To Metric

[pytelium]

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

[AAE]

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.

[Dave Hawley]

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.

[mikerickson]

You also might want to look at the CONVERT function.

[pytelium]

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.

[Dave Hawley]

Excel 2002 doesn't have that many rows

[pytelium]

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

[Marcel79]

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.

[Craig Ottley]

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

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

[pytelium]

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