Get 1st, 2nd, and 3rd Letter From a String
Author |
Message |
Brian47 #1 / 6
|
 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 |
|
 |
Chip Pearso #2 / 6
|
 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 |
|
 |
Bob Arnol #3 / 6
|
 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 |
|
 |
Ogilvy, Thomas, W., Mr., ODCSLO #4 / 6
|
 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 |
|
 |
Frank Isaac #5 / 6
|
 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 |
|
 |
Auke Reits #6 / 6
|
 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 |
|
|
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
|
|
|