Converting text to numbers

[David]

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.

 

[Trevor Shuttleworth]

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

 

[Dave Peterson]

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]

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

 

[David]

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


--

Dave Peterson
.
Hi Dave

Thats perfect !!! thanks for your help.
Regards
David