Check if cells in a range contain a single alphabetic character

Bill Hamilton

Board Regular
Joined
Mar 30, 2004
Messages
93
I have a named column of cells where I have to verify that each one contains any single lower or upper case alphabetic character, i.e. from 'a' to 'z' or from 'A' to 'Z'.

Can anyone help with a formula to do that?
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Try
Excel Formula:
=AND(LEN(range)=1,ABS(CODE(UPPER(range))-77.5)<13)
This will need to be array confirmed if not using office 365.
If empty cells are permitted then the formula will need to be modified to allow that. (if this is required please clarify if the cells would be empty, contain formula blanks, or a mixture of both).
 
Upvote 0
Thanks for the speedy response.

1. This could be run under any version of Excel from 2007 onwards but I'm developing this little project under 365 (Windows). Sorry, should have mentioned that. I have to admit I don't know what you mean by 'array confirmed', though, but it looks like it will be necessary.

2. Any or all cells in the range could be empty and there are no formulae. I was considering using =IF(COUNTA(range)>0,....) but that might not be necessary if your formula can allow for blanks.
 
Upvote 0
I've just tried your initial formula. If I type 'a' into the first cell in the range it reburns TRUE as expected. If I then type 'bb' into the second cell it remains TRUE and stays that way no matter what else is put into the range.

If I type 'aa' into the first cell it returns FALSE as expected, and remains that way regardless of any other values I put into in the range.

If I leave the first cell empty and put anything at all into any of the other cells in the range it returns #VALUE! (as it does if the whole range is empty).
 
Upvote 0
The unexpected results might be down to not being array confirmed. To do that, you need to enter the formula into the cell, then press Shift Ctrl + Enter together instead of just enter.
It will still cause issues with empty cells though, the CODE() part will trip up there on a #VALUE! error. I'll reply shortly with a new formula that will work with empty cells.

edit:-

Didn't take as long as I thought it would, needs to be array confirmed as above.
Excel Formula:
=AND(IF(LEN(A2:A10)=1,ABS(CODE(UPPER(A2:A10))-77.5)<13,LEN(A2:A10)=0))
Filled cells must contain 1 character a-z or A-Z, empty cells are ignored. Anything else in the range will return TRUE.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,148
Members
448,552
Latest member
WORKINGWITHNOLEADER

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