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.

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.

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



.

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