Announcement

Collapse
No announcement yet.

Convert Alphanumerics To Metric

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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
    AAE
    ----------------------------------------------------

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

    Comment


    • #3
      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.

      Comment


      • #4
        Re: Convert Alphanumerics To Metric

        You also might want to look at the CONVERT function.

        Comment


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

          Comment


          • #6
            Re: Convert Alphanumerics To Metric

            Excel 2002 doesn't have that many rows

            Comment


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

              Comment


              • #8
                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

                Comment


                • #9
                  Re: Convert Alphanumerics To Metric

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

                  Code:
                  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

                  Comment


                  • #10
                    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..

                    Comment


                    • #11
                      Re: Convert Alphanumerics To Metric

                      Here is another formula approach:

                      =(VALUE(LEFT($A2,FIND("m",$A2)-1))*8)+(IF(ISERROR(FIND("y",$A2)),0,IF(NOT(ISERROR(FIND("f",$A2))),VALUE(MID($A2,FIND("f",$A2)+1,LEN($A2)-FIND("f",$A2)-1)),IF(NOT(ISERROR(FIND("m",$A2))),VALUE(MID($A2,FIND("m",$A2)+1,LEN($A2)-FIND("m",$A2)-1)),IF(AND(ISERROR(FIND("m",$A2)),ISERROR(FIND("f",$A2))),VALUE(LEFT($A2,LEN($A2)-1)),0))))/220)+IF(ISERROR(FIND("f",$A2)),0,VALUE(MID($A2,FIND("m",$A2)+1,1)))
                      See attached.

                      The IF function is used to independently test for presence of miles, furlongs and yards.
                      It works well in the example WB you supplied and, hopefully, will work as well for the larger dataset.
                      Attached Files
                      AAE
                      ----------------------------------------------------

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

                      Comment


                      • #12
                        Re: Convert Alphanumerics To Metric

                        AAE,

                        The presence of a mile always needs to be in the results for your formula to work.

                        You also can't have a 2 digit furlong, the miles, furlongs and yards have to be in order.

                        UDF is the best approach.

                        Sorry to rain on your parade...

                        Craig

                        Comment


                        • #13
                          Re: Convert Alphanumerics To Metric

                          Thanks also AAE..since my data in this sheet always has miles first,your solution works fine..

                          Comment


                          • #14
                            Re: Convert Alphanumerics To Metric

                            AAE,
                            The presence of a mile always needs to be in the results for your formula to work.
                            I think you meant to say: "the presence of mile always needs to be in the source cell"

                            Well, I admit to not testing the formula for every scenario, which is why I stated "hopefully it will work for the larger data set." Guess I assumed the presence of miles would always be in the source values. The original formula does fall over if the source value is only yards (i.e. no miles or furlongs).

                            You also can't have a 2 digit furlong, the miles, furlongs and yards have to be in order.
                            My formula does not assume 2-digit furlongs. This element only looks for a single digit to the left of "f": =IF(ISERROR(FIND("f",$A2)),0,VALUE(MID($A2,FIND("f",$A2)-1,1)))
                            I don't understand what you mean by "have to be in order." The formula returns the correct results.
                            UDF is the best approach.
                            VBA is a "bolt-on" to Excel and while useful to accomplish many things, using built-in functons/formulas are typically faster than any VBA approach. I'm not a fan of mega-formulas like the one I provided, which is why I broke it down into separate columns, making it easier to understand and verify results, and used a simple formula for the final result.
                            Sorry to rain on your parade...

                            Craig
                            No problem here. Thanks for breaking the formula and pointing it out.

                            Here is the revised formula, now tested and shown to provide correct results when either miles, furlongs, yards, or any combination are missing in the string of the source value.
                            =IF(ISERR(FIND("m",$A2)),0,(VALUE(LEFT($A2,FIND("m",$A2)-1))*8))+IF(ISERR(FIND("f",$A2)),0,VALUE(MID($A2,FIND("f",$A2)-1,1)))+(IF(ISERR(FIND("y",$A2)),0,IF(NOT(ISERR(FIND("f",$A2))),VALUE(MID($A2,FIND("f",$A2)+1,LEN($A2)-FIND("f",$A2)-1)),IF(NOT(ISERR(FIND("m",$A2))),VALUE(MID($A2,FIND("m",$A2)+1,LEN($A2)-FIND("m",$A2)-1)),IF(AND(ISERR(FIND("m",$A2)),ISERR(FIND("f",$A2))),VALUE(LEFT($A2,LEN($A2)-1)),0))))/220)
                            Attached Files
                            AAE
                            ----------------------------------------------------

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

                            Comment


                            • #15


                              Re: Convert Alphanumerics To Metric

                              Originally posted by AAE
                              I don't understand what you mean by "have to be in order." The formula returns the correct results.
                              When i was looking at the initial question

                              Originally posted by pytelium
                              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 assumed too much on my part and steamed ahead thinking about the different permutations.

                              I come up with a small test plan.

                              First thing i did was test the source cell with all values present.
                              EG
                              2m3f110y

                              I then reversed the source cell

                              110y3f2m

                              I didn't know whether the source data would always be in a specific order hence i assumed the 'what if' logic. Based on that decision i come up with the VBA solution because i'm pretty crap at working out Array formulas.

                              So if you try changing the order of miles, furlongs and yards around you'll see the formula can't quite find what it's looking for because of the order in which the source cell needs to be in...

                              Hope this makes a bit more sense?

                              Craig

                              Comment

                              Working...
                              X