Auto capitalize text in column

mrkris1982

Active Member
Joined
Apr 16, 2009
Messages
407
How would I go about coding a worksheet to automatically capitalize any text entered into column J?

Id actually like for it to remove any spaces between characters and capitalize it if possible...

if I enter: brain fart 3
After pressing enter, saving or going to another cell, Id like for it to change to: BRAINFART3

Any chance this is possible?
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Try:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("J:J")) Is Nothing Then
    Target.Value = UCase(Application.Substitute(Target.Value, " ", ""))
End If
Application.EnableEvents = True
End Sub
 

j_latendre

Board Regular
Joined
Jan 21, 2011
Messages
101
Try this:

=SUBSTITUTE(UPPER($J1)," ","")

Draggen down, than you may copy and paste values over top of your j column.

Jesse
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Place this in the Sheet module
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 10 Then Exit Sub
Application.EnableEvents = False
Target = Replace(UCase(Target), " ", "")
Application.EnableEvents = True
End Sub

lenze
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536

ADVERTISEMENT

This should do it:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("J:J")<br>             <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Action if Condition(s) are met (do your thing here...)</SPAN><br>            Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>                Target.Value = UCase(Application.WorksheetFunction.Substitute(Target.Value, " ", ""))<br>            Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try this: right click the sheet tab, select View Code and paste in

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 10 Then
    Application.EnableEvents = False
    Target.Value = UCase(Replace(Target.Value, " ", ""))
    Application.EnableEvents = True
End If
End Sub
 

xbeatle

New Member
Joined
Jun 6, 2014
Messages
1
Thank you MrKowz as I came across your code and it seems to work..and I learned something..lol

Thanks again,

X..
 

Watch MrExcel Video

Forum statistics

Threads
1,109,518
Messages
5,529,309
Members
409,862
Latest member
lbisacca
Top