Identifying the first letter of the word....

ExcelNovice

Well-known Member
Joined
May 12, 2002
Messages
583
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.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
by "...the #1..." do you literally mean a 1 etc, or do you mean the first item in the range b3:b45?
 
Upvote 0
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.
 
Upvote 0
=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.
 
Upvote 0
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.
 
Upvote 0
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
ABCD
3cat1a
4dog0b
5ant1c
6horse1d
7cow1e
8elephant0f
9goat1g
10lion1h
11tiger0i
Starting Letters
 
Upvote 0

Forum statistics

Threads
1,214,891
Messages
6,122,105
Members
449,066
Latest member
Andyg666

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top