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?
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

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..
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,627
Messages
5,838,443
Members
430,548
Latest member
hh_dh2001

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
Top