Determining Upper/lower/Mixed case

J

jumpulas

Board Regular
Joined
Sep 18, 2004
Messages
80
Hello there,
I have the following:
Col A
Alpha
beta
Beta
BETA
zeta
Alpha2
alpha2

And would like to determine the results below:
Col A Col B
Alpha Mixed
beta Lower
Beta Mixed
BETA Upper
zeta Lower
Alpha2 Mixed_Numeric
aplha2 Lower_Numeric

Is there a way to do this in excel without VBA code?

regards
Jumpulas
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
I'm not sure how to determine if it contains numeric characters without VBA (unless a number would ALWAYS appear at the end, in which case it can be easily tested), but to get if it is Mixed, Lower, or Upper, you can use:

Excel 2003<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD>Alpha</TD><TD>Mixed</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD>beta</TD><TD>Lower</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD>Beta</TD><TD>Mixed</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD>BETA</TD><TD>Upper</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD>zeta</TD><TD>Lower</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD>Alpha2</TD><TD>Mixed</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">7</TD><TD>alpha2</TD><TD>Lower</TD></TR></TBODY></TABLE><TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH>Sheet1</TH></TR></TD></THEAD><TBODY></TBODY></TABLE>

<TABLE style="BORDER-RIGHT: black 2px solid; PADDING-RIGHT: 0.4em; BORDER-TOP: black 2px solid; PADDING-LEFT: 0.4em; PADDING-BOTTOM: 0.4em; BORDER-LEFT: black 2px solid; PADDING-TOP: 0.4em; BORDER-BOTTOM: black 2px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all width="85%"><TBODY><TR><TD style="PADDING-RIGHT: 6px; PADDING-LEFT: 6px; PADDING-BOTTOM: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center" cellPadding=2 rules=all width="100%"><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0"><TH width=10>Cell</TH><TH style="PADDING-LEFT: 5px; TEXT-ALIGN: left">Formula</TH></TR></THEAD><TBODY><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>B1</TH><TD style="TEXT-ALIGN: left">=IF(ISERROR(FIND(UPPER(A1),A1)),IF(ISERROR(FIND(LOWER(A1),A1)),IF(ISERROR(FIND(PROPER(A1),A1)),"","Mixed"),"Lower"),"Upper")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

If an alpha-numeric value would always end up having the number on the end, you can use:

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Alpha</td><td style=";">Mixed</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">beta</td><td style=";">Lower</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Beta</td><td style=";">Mixed</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">BETA</td><td style=";">Upper</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">zeta</td><td style=";">Lower</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Alpha2</td><td style=";">Mixed_Numeric</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">alpha2</td><td style=";">Lower_Numeric</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=IF(<font color="Blue">ISERROR(<font color="Red">FIND(<font color="Green">UPPER(<font color="Purple">A1</font>),A1</font>)</font>),IF(<font color="Red">ISERROR(<font color="Green">FIND(<font color="Purple">LOWER(<font color="Teal">A1</font>),A1</font>)</font>),IF(<font color="Green">ISERROR(<font color="Purple">FIND(<font color="Teal">PROPER(<font color="#FF00FF">A1</font>),A1</font>)</font>),"","Mixed"</font>),"Lower"</font>),"Upper"</font>) & IF(<font color="Blue">ISERROR(<font color="Red">RIGHT(<font color="Green">A1,1</font>)+0</font>),"","_Numeric"</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:
Upvote 0
Thanks MrKowz, appreciate it.

Regards
Jumpulas


MrKowz said:
I'm not sure how to determine if it contains numeric characters without VBA (unless a number would ALWAYS appear at the end, in which case it can be easily tested), but to get if it is Mixed, Lower, or Upper, you can use:

Excel 2003<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD>Alpha</TD><TD>Mixed</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD>beta</TD><TD>Lower</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD>Beta</TD><TD>Mixed</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD>BETA</TD><TD>Upper</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD>zeta</TD><TD>Lower</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD>Alpha2</TD><TD>Mixed</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">7</TD><TD>alpha2</TD><TD>Lower</TD></TR></TBODY></TABLE><TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH>Sheet1</TH></TR></TD></THEAD><TBODY></TBODY></TABLE>

<TABLE style="BORDER-RIGHT: black 2px solid; PADDING-RIGHT: 0.4em; BORDER-TOP: black 2px solid; PADDING-LEFT: 0.4em; PADDING-BOTTOM: 0.4em; BORDER-LEFT: black 2px solid; PADDING-TOP: 0.4em; BORDER-BOTTOM: black 2px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all width="85%"><TBODY><TR><TD style="PADDING-RIGHT: 6px; PADDING-LEFT: 6px; PADDING-BOTTOM: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center" cellPadding=2 rules=all width="100%"><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0"><TH width=10>Cell</TH><TH style="PADDING-LEFT: 5px; TEXT-ALIGN: left">Formula</TH></TR></THEAD><TBODY><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0" width=10>B1</TH><TD style="TEXT-ALIGN: left">=IF(ISERROR(FIND(UPPER(A1),A1)),IF(ISERROR(FIND(LOWER(A1),A1)),IF(ISERROR(FIND(PROPER(A1),A1)),"","Mixed"),"Lower"),"Upper")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

If an alpha-numeric value would always end up having the number on the end, you can use:

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Alpha</td><td style=";">Mixed</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">beta</td><td style=";">Lower</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Beta</td><td style=";">Mixed</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">BETA</td><td style=";">Upper</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">zeta</td><td style=";">Lower</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Alpha2</td><td style=";">Mixed_Numeric</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">alpha2</td><td style=";">Lower_Numeric</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=IF(<font color="Blue">ISERROR(<font color="Red">FIND(<font color="Green">UPPER(<font color="Purple">A1</font>),A1</font>)</font>),IF(<font color="Red">ISERROR(<font color="Green">FIND(<font color="Purple">LOWER(<font color="Teal">A1</font>),A1</font>)</font>),IF(<font color="Green">ISERROR(<font color="Purple">FIND(<font color="Teal">PROPER(<font color="#FF00FF">A1</font>),A1</font>)</font>),"","Mixed"</font>),"Lower"</font>),"Upper"</font>) & IF(<font color="Blue">ISERROR(<font color="Red">RIGHT(<font color="Green">A1,1</font>)+0</font>),"","_Numeric"</font>)</td></tr></tbody></table></td></tr></table><br />
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Find cells which start (not contain) search strings
Replies
5
Views
340
Alex Blakenburg
A
K
  • Question Question
Looking for assistance using MS Excel VBA to hide rows
Replies
4
Views
520
kcampbell675
K
E
  • Question Question
VBA code clean up
Replies
9
Views
580
mumps
mumps
JenniferMurphy
  • Question Question
Is there a keyboard shortcut to change ALL CAPS to All Caps?
2
Replies
14
Views
4K
MARK858
MARK858
T
  • Question Question
Flattening a hierarchy from 5 columns into a unique one
Replies
1
Views
760
Kerryx
K

Forum statistics

Threads
1,224,560
Messages
6,179,520
Members
452,922
Latest member
nstaab07

Share this page

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