Hi all,
I want to retrieve the alphabets of a word.
Ex: MY name is YOGESHWAR
now is there any formula so that I could find out
what is the first alphabet of YOGESHWAR ?
what is the second alphabet of YOGESHWAR ?
what is the third alphabet of YOGESHWAR ?
what is the fourth alphabet of YOGESHWAR and so on..........
Can somebody help me out on this task?
Yogeshwar
yogeshwar, by alphabet do you mean each letter in your name or the contents of an Excel cell?
[VBA]
Option Explicit
Sub GetLetters()
Dim N As Long, MyString As String
MyString = "yogeshwarv"
For N = 1 To Len(MyString)
Range("A" & N) = Right(Left(MyString, N), 1)
Next
End Sub
[/VBA]
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
Why not
[VBA]
Range("A" & N) = Mid(MyString, N, 1)
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
and=MID(A1,1,1)
where A1 is the name=MID(A1,2,1)
If the cell always have 9 characters then try..
First letter of the alphabet
=CHAR(SMALL(CODE(MID(A1,{1;2;3;4;5;6;7;8;9},1)),1))
Second letter of the alphabet
=CHAR(SMALL(CODE(MID(A1,{1;2;3;4;5;6;7;8;9},1)),2))
Third letter of the alphabet
=CHAR(SMALL(CODE(MID(A1,{1;2;3;4;5;6;7;8;9},1)),3))
SHAZAM!
And - for a little fun[vba]Sub GetLettersBackwards()
Dim N As Long, MyString As String
MyString = "YOGESHWAR"
For N = 1 To Len(MyString)
Range("A" & N) = Left(Right(MyString, N), 1)
Next
End Sub[/vba]
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
Well as long as were having fun, how about all the 4 letter word possibilities that could be derived from "YOGESHWAR"?Dave
ps. some of the word are pretty lame
edit:forgot option base
[VBA]
Option Explicit
Option Base 1
Sub RandWrdFrmName()
Dim AR() As Variant, MyString As String, N As Integer
Dim TotChars As Integer, Cnt As Integer, WdString As String
MyString = "YOGESHWAR"
ReDim AR(Len(MyString))
For N = 1 To Len(MyString)
AR(N) = Mid(MyString, N, 1)
Next
Randomize
'Totchars = Int((Len(MyString) * Rnd) + 1)'rndommize word length
TotChars = 4 ' make 4 letter words
Do
WdString = vbNullString
For Cnt = 1 To TotChars
WdString = WdString + AR(Int((Len(MyString)) * Rnd) + 1)
Next Cnt
Loop Until Application.CheckSpelling(WdString)
MsgBox "Your random " & TotChars & " character(s) word is: " & WdString
End Sub
[/VBA]
Last edited by Dave; 12-11-2006 at 10:48 AM. Reason: no option base 1
Well, in the name of fun how about random letter colors?
[vba]
Sub RandomColor()
Dim cell As Range, reverse As String
Randomize
For Count = 1 To ActiveCell.Characters.Count
ActiveCell.Characters(Count, 1).Font.Color = RGB(Int((255 * Rnd) + 1), Int((255 * Rnd) + 1), Int((255 * Rnd) + 1))
Next Count
End Sub
[/vba]
Get a grip children, the bloke is looking for assistance, not your amusement.
xld,
John solved this issue in his first post, now we are just having a little fun. RELAX, life's to long to be serious all the time.
Here's some revised code that randomize's the font color, but also display's each character from the cell. So fun, and serious all at the same time.
Cal
[vba]
Sub ShowCharacter()
Dim cell As Range, reverse As String
Randomize
For Count = 1 To ActiveCell.Characters.Count
MsgBox ActiveCell.Characters(Count, 1).text
'REMOVE THIS TO STOP RANDOM COLORING.
ActiveCell.Characters(Count, 1).Font.Color = RGB(Int((255 * Rnd) + 1), Int((255 * Rnd) + 1), Int((255 * Rnd) + 1))
Next Count
End sub
[/vba]
There are off-topic forums if you want a little fun. Personally I object to you over-loading a serious question with such drivel, trying to show us what a clever coder you are. It means that I waste my time reading it thinking that there might be some serious contribution, and the OP might well be turned off the forum thinking that he is not taken seriously.
Last edited by Bob Phillips; 12-11-2006 at 06:28 PM.
Bob has a point...I wouldn't want to offend someone if they thought we weren't taking his/her question seriously.
...Let's see what the OP says. If it were me, I would be excited to see what VBA could do and it might give me ideas for other coding tasks...but that's me, not yogeshwarv.
New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.
It also depends on exactly what Yogeshwar means by this. I took alphabets of a word' to mean 'letters of a word' and my original post gives the 1st, 2nd, 3rd, ..., etc letters counting from the left (and = Right(Left(MyString, N), 1) could be easily adapted to a worksheet formula if desired).Originally Posted by yogeshwarv
But what is really meant by 1st, 2nd, 3rd, ..., and so on? Is it 1st, 2nd, 3rd, ..., etc from the left, or 1st, 2nd, 3rd, ..., etc from the right?My second post redresses this by counting letters from the right (if that really was an issue, and = Left(Right(MyString, N), 1) can also be adapted)
However, Yogeshwar could also mean first retrieving, and then sorting the letters in alphabetical order (which could be done with a simple sort of either of those results)...
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
Hi,
Find attached a file, that will do it using..formula's only.
regards,
asingh
Hi Asingh,
Just a thought, but if you started your formula in Row 1 or Column 1 you could use Row() or Column() which avoids the need for the StringCounter
eg.
=IF(LEN($B$1) = 0,"",MID($B$1,(LEN($B$1)-LEN($B$1)+ROW()),1))
Regards
MD
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
HI,
Sorry for being late on this forum.
I believe XLD is correct this is exactly what I needed.
I knew there is formula in excel but I was not able to recall that.
I am new to VBA and I am trying to learn the same.
Thanks to every body who replied in this forum.
Basically, What i wanted to learn to very clear.
I wanted to know the 1st, 2nd, 3rd, 4th letter of alphabet in a word.... and so on..
Thanks for great help
Hi,
In B1 and copied across,
=IF(COLUMNS($B$1:B$1)<=LEN($A$1),MID($A$1,COLUMNS($B$1:B$1),1),"")
HTH
Posting Permissions
Reply With Quote
