Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Convert Alphanumerics To Metric

1. Senior Member
Join Date
23rd July 2005
Posts
184

## Convert Alphanumerics To Metric

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

Excel Video Tutorials / Excel Dashboards Reports

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.

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.

4. ## Re: Convert Alphanumerics To Metric

You also might want to look at the CONVERT function.

Excel Video Tutorials / Excel Dashboards Reports

5. Senior Member
Join Date
23rd July 2005
Posts
184

## Re: Convert Alphanumerics To Metric

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. ## Re: Convert Alphanumerics To Metric

Excel 2002 doesn't have that many rows

7. Senior Member
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. I agreed to these rules
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.

Excel Video Tutorials / Excel Dashboards Reports

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

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

Excel Video Tutorials / Excel Dashboards Reports

10. Senior Member
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

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

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