Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / 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
    24th 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,205

    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,784

    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,683

    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
    24th 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,784

  7. #7
    Join Date
    24th 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,377

    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. 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
    24th 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, 16:28
  2. Maximum Of Text & Numbers - Alphanumerics
    By ExcelUser777 in forum EXCEL HELP
    Replies: 2
    Last Post: July 31st, 2008, 14:35
  3. Increment Number & Text Of Alphanumerics
    By CLUBDEVEAU in forum EXCEL HELP
    Replies: 4
    Last Post: February 28th, 2008, 10:32
  4. Lookup Exact Alphanumerics
    By mattee6 in forum EXCEL HELP
    Replies: 3
    Last Post: December 16th, 2007, 10:37
  5. Extract Alphanumerics From String
    By EROEI in forum EXCEL HELP
    Replies: 15
    Last Post: July 30th, 2007, 14: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