Identifying the first letter of the word....

[ExcelNovice]

I have different words in cells B3 to B45.

I need a macro code or anything else that will work that places the #1 in cell C3 if none of the words in B3...B45 starts with the letter A; the #2 in C4 if none of the words in B3...B45 starts with the letter B and so on all the way to the letter Z.

Is this possible? Thanks for your help.

 

[PaddyD]

by "...the #1..." do you literally mean a 1 etc, or do you mean the first item in the range b3:b45?

 

[ExcelNovice]

PaddyD,
I need something like this in cell C3

If the letter A is found to be the first letter of any of the words in the range B3....B45, then 1, if not then 0.

Hope this makes it a little clearer....thanks for your help.

 

[babycody]

=IF(COUNTIF($A$1:$A$26,"a*")<>0,1,0) Where $A$1:$A$26 would be replaced by your range or dynamic named range (recommended if you intend to add to more information to the range), and "a*" would be "b*" in the next cell down. The $ sign is used to keep your range from changing. If you don't use these you will most certainly have problems with this formula. Hope this helps.

 

[ExcelNovice]

Works....thanks babycody.

 

[babycody]

Your welcome. If you want to see how many entries actually start with a certain letter just use the =COUNTIF($A$1:$A$26,"a*") part of my formula.

 

[Peter_SSs]

ExcelNovice

"..1 in cell C3 if none of the words in B3...B45 starts with the letter A" from your first post.

"If the letter A is found to be the first letter of any of the words in the range B3....B45, then 1..." from your second post.

These seem to contradict each other. However, it seems we are going wsith the second one. If you don't want to manually change the a to b to c to....z in the formulas, try this. In C3 (copied down to C28):
=IF(COUNTIF(B$3:B$45,CHAR(62+ROW())&"*"),1,0)

I have placed the letters in column D for checking purposes only. They play no part in getting the result.

Mr Excel.xls
	A	B	C	D
3		cat	1	a
4		dog	0	b
5		ant	1	c
6		horse	1	d
7		cow	1	e
8		elephant	0	f
9		goat	1	g
10		lion	1	h
11		tiger	0	i
Starting Letters