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

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

All good and well, but this doesn't fix the data entered in cell A1. It
only fixes the string created in A2. Is there a way I can fix what's in
A1 as well?


Arvi Laanemets Wrote:
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
------------------------------------------------------------------------
AMK4's Profile: http://www.excelforum.com/member.php...o&userid=19143
View this thread: http://www.excelforum.com/showthread...hreadid=501933

i fooled around a little with this, it may or may not do what you want.
right click a sheet name and choose view code
click the dropdown (general) and choose worksheet
paste this on the selection change sheet between private sub and end sub.
then type a name in column A



Dim cell As Range
If Target.Count = 1 Then

For Each cell In Range("A:A")
If cell.Value "" Then
If Not IsError(Application.Find(" ", cell.Value)) Then
cell.Offset(0, 1).Value = Left(Application.Proper(cell.Value), 2) & _
Mid(Application.Proper(cell.Value), Application.Find(" ", cell.Value) + _
1, 3)
End If
End If
Next
End If

--


Gary


"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

Aha! Thanks! It works... Now two things:

1) I noticed it being a bit slow and I can only assume it's because
it's running through the entire column each time one of the cells
change. Is there a way to have it only affect the changd cell as
opposed to scanning the whole column?

2) I will also be entering data in the 3rd column which I'd also like
to have .Proper run on. At the moment, the only way to do that is to
run yet another For loop, just like yours, to do that. This adds to
the time it takes updating the entire column.

If there's a way to update and affect just the (current) changed cell,
that'd be great. Suggestions?


Gary Keramidas Wrote:
i fooled around a little with this, it may or may not do what you want.
right click a sheet name and choose view code
click the dropdown (general) and choose worksheet
paste this on the selection change sheet between private sub and end
sub.
then type a name in column A


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

Hi

Into free column (row1) enter the formula like
=PROPER(A1)
and copy down.
Copy the range with formulas, right-click on A1, and select
PasteSpecialValues - OK.
Delete the column with formulas.

Modify the formula in column B, like
=IF(A1="","",LEFT(A1,MIN(2,FIND(" ",A1)-1))&MID(A1,FIND(" ",A1)+1,3))
(because you don't to use PROPER there anymore)


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



"AMK4" wrote in message
...

All good and well, but this doesn't fix the data entered in cell A1. It
only fixes the string created in A2. Is there a way I can fix what's in
A1 as well?


Arvi Laanemets Wrote:
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
------------------------------------------------------------------------
AMK4's Profile:
http://www.excelforum.com/member.php...o&userid=19143
View this thread: http://www.excelforum.com/showthread...hreadid=501933

see if this is any better. delete the other code or paste this over the top,
don't keep both

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
If Target.Count = 1 Then

If Not Intersect(Target, Range("A:A")) Is Nothing Then

If Not IsError(Application.Find(" ", Target.Value)) Then
Target.Offset(0, 1).Value = Left(Application.Proper(Target.Value), 2) & _
Mid(Application.Proper(Target.Value), Application.Find(" ", Target.Value) +
_
1, 3)
Target.Offset(0, 2).Value = Application.Proper(Target.Offset(0, 2).Value)
End If
End If
End If
End Sub

--


Gary


"AMK4" wrote in message
...

Aha! Thanks! It works... Now two things:

1) I noticed it being a bit slow and I can only assume it's because
it's running through the entire column each time one of the cells
change. Is there a way to have it only affect the changd cell as
opposed to scanning the whole column?

2) I will also be entering data in the 3rd column which I'd also like
to have .Proper run on. At the moment, the only way to do that is to
run yet another For loop, just like yours, to do that. This adds to
the time it takes updating the entire column.

If there's a way to update and affect just the (current) changed cell,
that'd be great. Suggestions?


Gary Keramidas Wrote:
i fooled around a little with this, it may or may not do what you want.
right click a sheet name and choose view code
click the dropdown (general) and choose worksheet
paste this on the selection change sheet between private sub and end
sub.
then type a name in column A


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

Thanks Gary. If I'm reading this correctly, then the C-column will only
get .Proper run on it when the A-column changes. While I'd like to be
able to do this, I can't enter data in both columns A and C at the same
time. Data may be entered in different ways, either everything in
A-column first, then stuff in C-column, or row by row. I just don't
know how people are going to work with this.

Would it matter if I duplicate the *If Not Intersect* loop a second
time, use *"C:C"* as the range and change the .Proper line to read
*Target.Value = Application.Proper(Target.Value)* or will that conflict
with the *Target* in the previous loop?


Gary Keramidas Wrote:
see if this is any better. delete the other code or paste this over the
top,
don't keep both

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
If Target.Count = 1 Then

If Not Intersect(Target, Range("A:A")) Is Nothing Then

If Not IsError(Application.Find(" ", Target.Value)) Then
Target.Offset(0, 1).Value = Left(Application.Proper(Target.Value), 2) &
_
Mid(Application.Proper(Target.Value), Application.Find(" ",
Target.Value) +
_
1, 3)
Target.Offset(0, 2).Value = Application.Proper(Target.Offset(0,
2).Value)
End If
End If
End If
End Sub

--


Gary


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

Ugh. This is driving me up the wall. I simply can't get it to work the
way I'd like it to. All of the suggestions given here are all good,
however they all work for the first step of the process. I need it to
be expanded and optimized if you will. Right now it runs the entire
course of a column (I think) and thus causes a delay when you type a
name in and hit return.

Picture 3 columns, D, E, and F, each one 30 rows (4:34). Column D will
be used to enter a person's name. Column E will be used to
programmatically generate a string consisting of 2 and 3 letters of
their first and last name respectively. And Column F will be used to
enter the name of that person's parents.

When I enter a name in Column D, I want to run:
Code:
--------------------
::
cell.Value = Application.Proper(cell.Value)
' Fill E column with 2+3 string
cell.Offset(0, 1).Value = Left(call.Value, 2) & _
mid(cell.Value), Application.Find(\" \", cell.Value) + 1, 3)::
--------------------


That's it. Now, when I enter a name in Column F, I want to run:
Code:
--------------------
::
cell.Value = Application.Proper(cell.Value)::
--------------------


And that's it. One more step to finish it all. If I delete a name
from Column D, I want to run:
Code:
--------------------
::
cell.Offset(0, 1).Value = \"\"
cell.Offset(0, 2).Value = \"\"::
--------------------


Basically, clear out the other two cells as well. All of this on a
cell change I guess, which at the moment I've been trying to make work
by having a Worksheet_Change() sub. But I can't for the life of me
figure out how to do this at the same time. Any help would be greatly
appreciated.


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

see if this works like you want, then i'll work on blanking out the entres



Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

If Target.Count = 1 Then

If Not Intersect(Target, Range("D4:D34")) Is Nothing Then

If Not IsError(Application.Find(" ", Target.Value)) Then
Target.Offset(0, 1).Value = Left(Application.Proper(Target.Value), 2) & _
Mid(Application.Proper(Target.Value), Application.Find(" ", Target.Value) +
_
1, 3)
End If
End If
If Not Intersect(Target, Range("f4:f34")) Is Nothing Then
Target.Value = Application.Proper(Target.Value)

End If
End If
End Sub


--


Gary


"AMK4" wrote in message
...

Ugh. This is driving me up the wall. I simply can't get it to work the
way I'd like it to. All of the suggestions given here are all good,
however they all work for the first step of the process. I need it to
be expanded and optimized if you will. Right now it runs the entire
course of a column (I think) and thus causes a delay when you type a
name in and hit return.

Picture 3 columns, D, E, and F, each one 30 rows (4:34). Column D will
be used to enter a person's name. Column E will be used to
programmatically generate a string consisting of 2 and 3 letters of
their first and last name respectively. And Column F will be used to
enter the name of that person's parents.

When I enter a name in Column D, I want to run:
Code:
--------------------
::
cell.Value = Application.Proper(cell.Value)
' Fill E column with 2+3 string
cell.Offset(0, 1).Value = Left(call.Value, 2) & _
mid(cell.Value), Application.Find(\" \", cell.Value) + 1, 3)::
--------------------


That's it. Now, when I enter a name in Column F, I want to run:
Code:
--------------------
::
cell.Value = Application.Proper(cell.Value)::
--------------------


And that's it. One more step to finish it all. If I delete a name
from Column D, I want to run:
Code:
--------------------
::
cell.Offset(0, 1).Value = \"\"
cell.Offset(0, 2).Value = \"\"::
--------------------


Basically, clear out the other two cells as well. All of this on a
cell change I guess, which at the moment I've been trying to make work
by having a Worksheet_Change() sub. But I can't for the life of me
figure out how to do this at the same time. Any help would be greatly
appreciated.


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

try this and see how it works for you:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range

If Target.Count = 1 Then

If Not Intersect(Target, Range("D4:D34")) Is Nothing Then

If Not IsError(Application.Find(" ", Target.Value)) Then
Target.Offset(0, 1).Value = Left(Application.Proper(Target.Value), 2) & _
Mid(Application.Proper(Target.Value), Application.Find(" ", Target.Value) +
_
1, 3)
ElseIf Target.Value = "" Then
Range(Cells(Target.Row, "d"), Cells(Target.Row, "f")) = ""
End If
End If
If Not Intersect(Target, Range("f4:f34")) Is Nothing Then
Target.Value = Application.Proper(Target.Value)

End If
End If
End Sub


--


Gary


"AMK4" wrote in message
...

Ugh. This is driving me up the wall. I simply can't get it to work the
way I'd like it to. All of the suggestions given here are all good,
however they all work for the first step of the process. I need it to
be expanded and optimized if you will. Right now it runs the entire
course of a column (I think) and thus causes a delay when you type a
name in and hit return.

Picture 3 columns, D, E, and F, each one 30 rows (4:34). Column D will
be used to enter a person's name. Column E will be used to
programmatically generate a string consisting of 2 and 3 letters of
their first and last name respectively. And Column F will be used to
enter the name of that person's parents.

When I enter a name in Column D, I want to run:
Code:
--------------------
::
cell.Value = Application.Proper(cell.Value)
' Fill E column with 2+3 string
cell.Offset(0, 1).Value = Left(call.Value, 2) & _
mid(cell.Value), Application.Find(\" \", cell.Value) + 1, 3)::
--------------------


That's it. Now, when I enter a name in Column F, I want to run:
Code:
--------------------
::
cell.Value = Application.Proper(cell.Value)::
--------------------


And that's it. One more step to finish it all. If I delete a name
from Column D, I want to run:
Code:
--------------------
::
cell.Offset(0, 1).Value = \"\"
cell.Offset(0, 2).Value = \"\"::
--------------------


Basically, clear out the other two cells as well. All of this on a
cell change I guess, which at the moment I've been trying to make work
by having a Worksheet_Change() sub. But I can't for the life of me
figure out how to do this at the same time. Any help would be greatly
appreciated.


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

Gary Keramidas Wrote:
try this and see how it works for you:

Garry,

It works, except it doesn't run the Target cell through
Application.Proper as well. And I think that's where the delay problem
comes into play. Since a) I'm making a change by typing something in,
and b) the macro itself makes a second change when it runs Proper on
it. I don't know how to avoid that delay (or recursive loop if that's
what it is.) But when I stick a MsgBox line in the If Then statement
and run the macro, that MsgBox comes up a a whole lot more than those
30 rows before it finally quits (I stopped counting at 100 times)...


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

don't know why it does that for data in column F. i don't see any slowdown,
though, probably because i have an athlon64 x2. it seems to iterate 199
times.
tom or bob, or norman or one of those others may be able to shed some light
on it.
--


Gary


"AMK4" wrote in message
...

Gary Keramidas Wrote:
try this and see how it works for you:

Garry,

It works, except it doesn't run the Target cell through
Application.Proper as well. And I think that's where the delay problem
comes into play. Since a) I'm making a change by typing something in,
and b) the macro itself makes a second change when it runs Proper on
it. I don't know how to avoid that delay (or recursive loop if that's
what it is.) But when I stick a MsgBox line in the If Then statement
and run the macro, that MsgBox comes up a a whole lot more than those
30 rows before it finally quits (I stopped counting at 100 times)...


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

Gary Keramidas Wrote:
don't know why it does that for data in column F. i don't see an
slowdown,
though, probably because i have an athlon64 x2. it seems to iterat
199
times.
tom or bob, or norman or one of those others may be able to shed som
light
on it.

Yes, entering data in column F will cause that iteration...for whateve
reason. But that's not what I was talking about. I want to have th
first Target cell (column D) to also have Application.Proper run on.
And as soon as I add that extra bit in the If ... End If bit, I star
getting iterations. And as you noticed, the same thing happens wit
column F.

I hope someone has some way around this

--
AMK
-----------------------------------------------------------------------
AMK4's Profile: http://www.excelforum.com/member.php...fo&userid=1914
View this thread: http://www.excelforum.com/showthread.php?threadid=50193

Hi AMK4,

Try:

On Error GoTo XIT
Appplication.EnableEvents = False

'Your code

XIT:
Appplication.EnableEvents = True


---
Regards,
Norman



"AMK4" wrote in message
...

Gary Keramidas Wrote:
don't know why it does that for data in column F. i don't see any
slowdown,
though, probably because i have an athlon64 x2. it seems to iterate
199
times.
tom or bob, or norman or one of those others may be able to shed some
light
on it.

Yes, entering data in column F will cause that iteration...for whatever
reason. But that's not what I was talking about. I want to have the
first Target cell (column D) to also have Application.Proper run on.
And as soon as I add that extra bit in the If ... End If bit, I start
getting iterations. And as you noticed, the same thing happens with
column F.

I hope someone has some way around this.


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

Hi AMK4.

Appplication === Application


---
Regards,
Norman


"Norman Jones" wrote in message
...
Hi AMK4,

Try:

On Error GoTo XIT
Appplication.EnableEvents = False

'Your code

XIT:
Appplication.EnableEvents = True


---
Regards,
Norman

i never got iterations with any other part, only column F. everything worked
here as you asked, but i didn't notice the extra iterations on my pc,
because it happens so fast.


--


Gary


"AMK4" wrote in message
...

Gary Keramidas Wrote:
don't know why it does that for data in column F. i don't see any
slowdown,
though, probably because i have an athlon64 x2. it seems to iterate
199
times.
tom or bob, or norman or one of those others may be able to shed some
light
on it.

Yes, entering data in column F will cause that iteration...for whatever
reason. But that's not what I was talking about. I want to have the
first Target cell (column D) to also have Application.Proper run on.
And as soon as I add that extra bit in the If ... End If bit, I start
getting iterations. And as you noticed, the same thing happens with
column F.

I hope someone has some way around this.


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

norman's tip seems to work here. thanks norman

--


Gary


"Norman Jones" wrote in message
...
Hi AMK4.

Appplication === Application


---
Regards,
Norman


"Norman Jones" wrote in message
...
Hi AMK4,

Try:

On Error GoTo XIT
Appplication.EnableEvents = False

'Your code

XIT:
Appplication.EnableEvents = True


---
Regards,
Norman

and watch your p's and q's, norman <g

--


Gary


"Norman Jones" wrote in message
...
Hi AMK4.

Appplication === Application


---
Regards,
Norman


"Norman Jones" wrote in message
...
Hi AMK4,

Try:

On Error GoTo XIT
Appplication.EnableEvents = False

'Your code

XIT:
Appplication.EnableEvents = True


---
Regards,
Norman

Norman? You're a genius! Works like a charm! (after the removal of
stowaway p's)

Thank you, thank you, thank you!


Norman Jones Wrote:
Hi AMK4,

Try:

On Error GoTo XIT
Appplication.EnableEvents = False

'Your code

XIT:
Appplication.EnableEvents = True


---
Regards,
Norman


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