Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Converting text to numbers
How do I convert say 1m5f109y into yards where "m" =
1*1760 "f" = 5*220 and "y" = 109, which is 2969 yards in total.I thought of a Lookup Table but that would involve hundreds of lines.The distances would vary from 5f the lowest to 2m7f219y the highest.Any help would be appreciated. |
#2
|
|||
|
|||
David
one way: =(--LEFT(A1,FIND("m",A1)-1)*1760) + (--MID(A1,FIND("m",A1)+1,FIND("f",A1)-FIND("m",A1)-1)*220) + (--MID(A1,FIND("f",A1)+1,FIND("y",A1)-FIND("f",A1)-1)) 2m7f219y = 5279 You would need to enter the data in the format aaambfcccy otherwise the find(s) will fail and you'd get #VALUE! You could maybe put some error checking in as a fail safe but it would be easier to enter 2m0f22y without error checking than 2m22y with error checking Regards Trevor "David" wrote in message ... How do I convert say 1m5f109y into yards where "m" = 1*1760 "f" = 5*220 and "y" = 109, which is 2969 yards in total.I thought of a Lookup Table but that would involve hundreds of lines.The distances would vary from 5f the lowest to 2m7f219y the highest.Any help would be appreciated. |
#3
|
|||
|
|||
I'd use a userdefined function:
Option Explicit Function myConversion(myStr As String) As Variant Dim res As Variant myStr = LCase(myStr) myStr = Application.Substitute(myStr, "m", "*1760+") myStr = Application.Substitute(myStr, "f", "*220+") myStr = Application.Substitute(myStr, "y", "") If Right(myStr, 1) = "+" Then myStr = Left(myStr, Len(myStr) - 1) End If res = Application.Evaluate(myStr) If IsError(res) Then myConversion = "Format Error" Else myConversion = res End If End Function ========== If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Short course: Open your workbook. Hit alt-f11 to get to the VBE (where macros/UDF's live) hit ctrl-R to view the project explorer Find your workbook. should look like: VBAProject (yourfilename.xls) right click on the project name Insert, then Module You should see the code window pop up on the right hand side Paste the code in there. Now go back to excel. Into a test cell and type: =myconversion(a1) David wrote: How do I convert say 1m5f109y into yards where "m" = 1*1760 "f" = 5*220 and "y" = 109, which is 2969 yards in total.I thought of a Lookup Table but that would involve hundreds of lines.The distances would vary from 5f the lowest to 2m7f219y the highest.Any help would be appreciated. -- Dave Peterson |
#4
|
|||
|
|||
Hi Trevor
Thanks for your quick response. Your function works perfectly,could you possibly help me with the error checking part of it.My experience of macros and functions is very limited. Regards David -----Original Message----- David one way: =(--LEFT(A1,FIND("m",A1)-1)*1760) + (--MID(A1,FIND("m",A1)+1,FIND("f",A1)-FIND("m",A1)-1) *220) + (--MID(A1,FIND("f",A1)+1,FIND("y",A1)-FIND("f",A1)-1)) 2m7f219y = 5279 You would need to enter the data in the format aaambfcccy otherwise the find(s) will fail and you'd get #VALUE! You could maybe put some error checking in as a fail safe but it would be easier to enter 2m0f22y without error checking than 2m22y with error checking Regards Trevor "David" wrote in message ... How do I convert say 1m5f109y into yards where "m" = 1*1760 "f" = 5*220 and "y" = 109, which is 2969 yards in total.I thought of a Lookup Table but that would involve hundreds of lines.The distances would vary from 5f the lowest to 2m7f219y the highest.Any help would be appreciated. . |
#5
|
|||
|
|||
-----Original Message----- I'd use a userdefined function: Option Explicit Function myConversion(myStr As String) As Variant Dim res As Variant myStr = LCase(myStr) myStr = Application.Substitute(myStr, "m", "*1760+") myStr = Application.Substitute(myStr, "f", "*220+") myStr = Application.Substitute(myStr, "y", "") If Right(myStr, 1) = "+" Then myStr = Left(myStr, Len(myStr) - 1) End If res = Application.Evaluate(myStr) If IsError(res) Then myConversion = "Format Error" Else myConversion = res End If End Function ========== If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Short course: Open your workbook. Hit alt-f11 to get to the VBE (where macros/UDF's live) hit ctrl-R to view the project explorer Find your workbook. should look like: VBAProject (yourfilename.xls) right click on the project name Insert, then Module You should see the code window pop up on the right hand side Paste the code in there. Now go back to excel. Into a test cell and type: =myconversion(a1) David wrote: How do I convert say 1m5f109y into yards where "m" = 1*1760 "f" = 5*220 and "y" = 109, which is 2969 yards in total.I thought of a Lookup Table but that would involve hundreds of lines.The distances would vary from 5f the lowest to 2m7f219y the highest.Any help would be appreciated. -- Dave Peterson . Hi Dave Thats perfect !!! thanks for your help. Regards David |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting selected section of text to numbers | Excel Discussion (Misc queries) | |||
Converting Numbers to Text properly | Excel Discussion (Misc queries) | |||
roundoff when converting text to numbers | Excel Worksheet Functions | |||
converting numbers to text and prefill text field with 0's | Excel Discussion (Misc queries) | |||
Converting text to numbers | Excel Discussion (Misc queries) |