Get 1st, 2nd, and 3rd Letter From a String
Author Message
Get 1st, 2nd, and 3rd Letter From a String

I have a string which will be text. Im trying to convert it to Numbers.
That is, A-Z = 1-26.  blank = 0
For Example,
Cell A1 = Brian >> Cell A2 = 2189114. (The equivalent of brian in numbers)
so Brian: B = 2, R = 18, I = 9, A = 1, N = 14.

Any Ideas?

Brian

Fri, 31 Aug 2001 03:00:00 GMT
Get 1st, 2nd, and 3rd Letter From a String
Brian,

Try  something like this:

Dim Ndx As Integer
Dim Result As String
For Ndx = 1 To Len(Selection.Text)
Result = Result & Asc(UCase(Mid(Selection.Text, Ndx, 1))) - 64
Next Ndx
MsgBox Result

Cordially,
Chip Pearson
http://home.gvi.net/~cpearson/excel

Quote:
>I have a string which will be text. Im trying to convert it to
Numbers.
>That is, A-Z = 1-26.  blank = 0
>For Example,
>Cell A1 = Brian >> Cell A2 = 2189114. (The equivalent of brian in
numbers)
>so Brian: B = 2, R = 18, I = 9, A = 1, N = 14.

>Any Ideas?

>Brian

Fri, 31 Aug 2001 03:00:00 GMT
Get 1st, 2nd, and 3rd Letter From a String
it is impossible to translate. Regards, Bob.

Quote:
> I have a string which will be text. Im trying to convert it to Numbers.
> That is, A-Z = 1-26.  blank = 0
> For Example,
> Cell A1 = Brian >> Cell A2 = 2189114. (The equivalent of brian in
numbers)
> so Brian: B = 2, R = 18, I = 9, A = 1, N = 14.

> Any Ideas?

> Brian

Fri, 31 Aug 2001 03:00:00 GMT
Get 1st, 2nd, and 3rd Letter From a String
Brian,

Here is something to get you started:

Sub test2()
svar = "C5"
sText = UCase(Range(svar).Value)
sNewText = ""
For i = 1 To Len(sText)
icode = Asc(Mid(sText, i, 1))
If icode >= 65 And icode <= 64 + 26 Then
sNewText = sNewText & Asc(Mid(sText, i, 1)) - 64
Else
sNewText = sNewText & "*"
End If
Next i
Range(svar)(1, 2).Value = sNewText
End Sub

You will have to sort out how you want to handle case, special
characters and numbers.

Regards,
Tom Ogilvy

Quote:
-----Original Message-----

Posted At: Monday, March 15, 1999 3:16 PM
Posted To: programming
Conversation: Get 1st, 2nd, and 3rd Letter From a String
Subject: Get 1st, 2nd, and 3rd Letter From a String

I have a string which will be text. Im trying to convert it to Numbers.
That is, A-Z = 1-26.  blank = 0
For Example,
Cell A1 = Brian >> Cell A2 = 2189114. (The equivalent of brian in
numbers)
so Brian: B = 2, R = 18, I = 9, A = 1, N = 14.

Any Ideas?

Brian

Fri, 31 Aug 2001 03:00:00 GMT
Get 1st, 2nd, and 3rd Letter From a String
This question provides a few interesting possiblities.

1) Here's sort of a start:
=CODE(UPPER(MID(A3,1,1)))-64&CODE(UPPER(MID(A3,2,1)))-64
This is the most direct and simple approach. Unfortunately, a formula has no
convenient way of knowing how many characters are in the string, so you'll
probably run into problems. Potentially, you can solve them by making the
formula look at enough letters to cover the longest case, and by putting a
blank when the return value for a particular character is invalid, as in the
6th character of a 5-character string, but that is tedious at best. (You
could TRIM that result if you decide to go that way.)

2) Another option which is probably a bit slower in terms of performance but
a bit easier to manipulate:
=SEARCH(UPPER(MID(A3,1,1)),Alpha)&SEARCH(UPPER(MID(A3,2,1)),Alpha)
(This assumes a range named "Alpha" containing the alphabet.)
I don't think this is a great option but I provide it as an alternative
which may prove applicable to your situation.

3) There's always VBA!

Function CodeIt(rngName As Excel.Range) As String
Dim iX As Integer, iVal As Integer

For iX = 1 To Len(rngName)
iVal = Asc(UCase(Mid(rngName, iX, 1))) - 64
If iVal < 1 Or iVal > 26 Then
CodeIt = CodeIt & "0"
Else
CodeIt = CodeIt & CStr(iVal)
End If
Next
End Function

Paste the function into a VBA module and use a formula like:

=CodeIt(A1)

Any character outside A-Z is converted to a zero.

Note that it would theoretically be faster to return as a numeric instead of
a string, since the Asc function returns a number and it wouldn't have to be
converted each time to its string equivalent. However, without knowing how
long the string is, it might exceed the numeric capacity, and there's the

HTH -

-Frank
Microsoft Excel MVP, MCSD
Dolphin Technology Corp.
http://vbapro.com

Quote:

>I have a string which will be text. Im trying to convert it to Numbers.
>That is, A-Z = 1-26.  blank = 0
>For Example,
>Cell A1 = Brian >> Cell A2 = 2189114. (The equivalent of brian in numbers)
>so Brian: B = 2, R = 18, I = 9, A = 1, N = 14.

>Any Ideas?

>Brian

Fri, 31 Aug 2001 03:00:00 GMT
Get 1st, 2nd, and 3rd Letter From a String

Quote:
> I have a string which will be text. Im trying to convert it to Numbers.
> That is, A-Z = 1-26.  blank = 0
> For Example,
> Cell A1 = Brian >> Cell A2 = 2189114. (The equivalent of brian in numbers)
> so Brian: B = 2, R = 18, I = 9, A = 1, N = 14.

Take a look a Mid() and Asc() in the help

Greetings from
_____
/_|__| Auke Reitsma, Delft, The Netherlands.
/  | \  -------------------------------------
Remove NO_SPAM_ from my address ...

Sat, 01 Sep 2001 03:00:00 GMT

 Page 1 of 1 [ 6 post ]

Relevant Pages