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?

Thanks in advance for your help.
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?

>Thanks in advance for your help.
>Brian



Fri, 31 Aug 2001 03:00:00 GMT  
 Get 1st, 2nd, and 3rd Letter From a String
Or your string could be translated to "BAHIAAD" ---w/o character delimiters
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?

> Thanks in advance for your help.
> 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?

Thanks in advance for your help.
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
issue of leading blanks/spaces/zeroes.

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?

>Thanks in advance for your help.
>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  
 
 [ 6 post ] 

 Relevant Pages 

1. Displaying: date: 1st, 2nd, 3rd, 11th, 21st

2. 1st box<2nd box<3rd box?

3. Return 1st,2nd,3rd,4th from Date

4. 1st month, 2nd month, 3rd month...

5. If 1st cell is empty, I want data from 2nd cell to copy into 3rd cell

6. Displaying Day as 1st, 2nd, 3rd, etc.

7. Postions - 1st 2nd 3rd 4th etc

8. 1st and 2nd letter

9. Triggering 2nd show on 2nd PC from 1st show

10. Excel Doesn't Capitalize 1st Letter of 1st Sentence


 
Powered by phpBB® Forum Software © phpBB Group