Hi

=IF(A1="","",LEFT(PROPER(A1),MIN(2,FIND(" ",A1)-1))&MID(PROPER(A1),FIND("
",A1)+1,3))

The formula above works for cases the first name is 1 or 2 characters too,
but does fail when a single name is in cell A1


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"AMK4" wrote in message
...

How can I change the case of text typed into a cell as soon as the cell
loses its focus? Basically I have a sheet that requires one to enter
names in a column, first and last. Upon exiting that cell (or it
losing its focus), that data then gets used in the very next cell to
generate a sring consisting of the first 2 letters of the first name
and first 3 letters of the last name. What I need to do is make sure
the case is proper before I use the data in the next cell.

Correct input in A1: Jack Doppler
Resulting text in A2: JaDop

Wrong input in A1: jACk dOPplEr
Resulting test in A2: JaDop

Either I figure out a way to fix the text that they just typed in as
soon as the cell loses its focus (preferred), or I fix the resulting
string when it gets created, AND fix the text in A1 at the same time.

For reference, cell A2 contains the following formula:

Code:
--------------------
=IF(ISBLANK(A1),0,CONCATENATE(MID(TRIM(A1), 1, 2), MID(TRIM(A1), FIND("
", TRIM(A1)) + 1, 3)))
--------------------
..which checks whether A1 is empty before doing anything. Then it
creates the string.


--
AMK4
------------------------------------------------------------------------
AMK4's Profile:
http://www.excelforum.com/member.php...o&userid=19143
View this thread: http://www.excelforum.com/showthread...hreadid=501933