PDA

View Full Version : How to retrieve the alphabets of a word



yogeshwarv
12-11-2006, 05:23 AM
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

OBP
12-11-2006, 05:37 AM
yogeshwar, by alphabet do you mean each letter in your name or the contents of an Excel cell?

johnske
12-11-2006, 06:04 AM
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

mdmackillop
12-11-2006, 06:44 AM
Why not

Range("A" & N) = Mid(MyString, N, 1)

:think:

Bob Phillips
12-11-2006, 07:14 AM
=MID(A1,1,1)

and


=MID(A1,2,1)

where A1 is the name

Shazam
12-11-2006, 08:13 AM
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))

johnske
12-11-2006, 08:46 AM
And - for a little fun :devil2: 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

Dave
12-11-2006, 10:36 AM
Well as long as were having fun, how about all the 4 letter word possibilities that could be derived from "YOGESHWAR"? :yes Dave
ps. some of the word are pretty lame

edit:forgot option base

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

CBrine
12-11-2006, 12:53 PM
Well, in the name of fun how about random letter colors?

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

Bob Phillips
12-11-2006, 01:30 PM
Get a grip children, the bloke is looking for assistance, not your amusement.

CBrine
12-11-2006, 01:58 PM
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.
:biggrin: :bug:

Cal


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

Bob Phillips
12-11-2006, 04:16 PM
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.

malik641
12-11-2006, 06:17 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.

johnske
12-11-2006, 06:56 PM
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?

YogeshwarIt 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).

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? :dunno 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)...:dunno

asingh
12-12-2006, 03:16 AM
Hi,

Find attached a file, that will do it using..formula's only.


regards,

asingh

mdmackillop
12-12-2006, 06:30 AM
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

yogeshwarv
12-12-2006, 01:15 PM
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

Krishna Kumar
12-13-2006, 02:05 AM
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