How to convert Text to Number Function

[Oded Dror]

Hi there,

I'm using Excel 2000 and I have a price list looking like this

A = 1
B = 2
C = 3
D = 4
E = 5
F = 6
G = 7
H = 8
I = 9
J = 0
K = . (my be I can avoid that)

now I have an Item look like BFKBE which is 26.25

My question is how to convert this letters into a number ?

or JBKII which is 2.99

Thanks,

Oded Dror
Email: (e-mail address removed)

 

[Rob van Gelder]

Sub test()
Dim i As Long, strText As String, strNum As String

strText = "BFKBE" '"JBKII"
strNum = vbNullString

For i = 1 To Len(strText)
Select Case Asc(Mid(strText, i, 1))
Case Asc("A") To Asc("J"): strNum = strNum & _
(Asc(Mid(strText, i, 1)) - 64) Mod 10
Case Asc("K"): strNum = strNum & "."
End Select
Next

MsgBox CDbl(strNum)
End Sub

 

[Paul D]

while Rob's code is much nicer, here is another way. This allows you to use
this as a function in the worksheet

Public Function ConvertString(strPrice As String)
Dim tcost As Integer, cost As String, i As Integer
For i = 1 To Len(strPrice)
tcost = Asc(Mid(strPrice, i, 1)) - 64
If tcost < 10 Then
cost = cost & tcost
ElseIf tcost = 10 Then
cost = cost & "0"
ElseIf tcost = 11 Then
cost = cost & "."
End If
Next i
ConvertString = CDec(cost)
End Function

Paul D

 

[Dana DeLouis]

A slight variation to your excellent code might be something like this.
This idea uses Case "A" To "J"

Sub Demo()
Dim i As Long
Dim s As String
Dim strText As String
Dim strNum As String

strText = "BFKBE"

For i = 1 To Len(strText)
s = Mid$(strText, i, 1)
Select Case s
Case "A" To "J"
strNum = strNum & (Asc(s) - 64) Mod 10
Case "K"
strNum = strNum & "."
Case Else
MsgBox "Unknown symbol: " & s
End Select
Next i

MsgBox CDbl(strNum)
End Sub

 

[Rob van Gelder]

Nice.
I think I expecting too much from Select Case as I was writing and ended up
with bloat.
Your code is much more elegant.


Yet another approach, which assumes strText has no invalid letters

Sub test()
Dim i As Long, str As String, strText As String, strNum As String

strText = "BFKBE"

For i = 1 To Len(strText)
str = Mid(strText, i, 1)
strNum = strNum & IIf(str = "K", ".", (Asc(str) - 64) Mod 10)
Next

MsgBox CDbl(strNum)
End Sub