• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Force upper case

You will have to consider VBA route. Either an event based code [Worksheet_Change] or Standard Routine which you have to run manually.
 
Ajit


Can you walk us through the work flow of getting the data into your worksheet?
 
Hi Ajit ,


If it only text , the simplest way is :


1. Copy the text into Word

2. Select the text.

3. Right click , select Font , ALL CAPS , OK.

4. Copy this upper case text to Excel.


Narayan


Taken from : http://forums.techguy.org/business-applications/220015-converting-excel-table-all-caps.html
 
Thanks you all guys,

See, i regularly get data in a excel file or through mails in microsoft outlook which may or may not be in caps.This data is consolidated (Copy and pasted by me )in to an excel worksheet. For clarity purpose or for good appearance, the moment i paste the data ,I want all the data to be in upper case automatically/by default.


Please suggest if possible to do without a VBA code(As i'm not conversant with VBA )


Thanks once again
 
The easiest way would be to have an input sheet where the data is pasted into

Then have a transfer sheet which will using formulas take the values in the Input sheet and transfer them to the Transfer sheet in the correct case

Then use the Transfer sheet for your calculations/reports etc


This would allow data to be added to, or replaced on, the Input Sheet as appropriate
 
Good day Ajit


Could you not use helper columns and the use the UPPER function to convert text then just copy and paste OVER THE TOP of the original data??
 
Ajit As the old saying goes there is more than one way to skin a cat so have a look at these methods......but make sure your knife is sharp

[pre]
Code: 
Excel has formulas for converting a bunch of text to UPPER, LOWER and PROPER Cases. But not a formula to convert a Sentence case.
Upper case: MAKE ME A SANWICH  =UPPER(C2)

Lower case: make me a sandwich  =LOWER(C2)

Proper case: Make Me A Sandwich =PROPER(C2)

[pre][code]Sentence case: Make me a sandwich =UPPER(LEFT(C2,1))&MID(LOWER(C2),2,999
Assuming your text is in C2, the formula is,

=UPPER(LEFT(C2,1))&MID(LOWER(C2),2,999)
[/pre]
How the formula works?

It is converting first letter of C2 to upper case [UPPER(LEFT(C2,1))] and lower case of rest of C2 [MID(LOWER(C2),2,999)]

The 999 portion is code to unlock features of MID formula.

The 999 is just a cop-out to say give me rest of the C2, I don’t know the length of it. (of course, 999 would fail if your text actually has more than 1000 chars. In that case, just use 9999 or whatever large number you fancy.)

Another formula:[/code][/pre]
=SUBSTITUTE(LOWER(C2),CHAR(CODE(C2)),UPPER(CHAR(CODE(C2))),1)

The above formulas assume that C2 has one sentence
 
Hi, Ajit!


Try copying the following code and pasting it into the code area of 'ThisWorkbook' object within your VBA editor (Alt-F11, double click in proper workbook, double click on ThisWorbook, then go to the right window pane):

-----

[pre]
Code: 
Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
' constants
' declarations
Dim c As Range
' start
' process
For Each c In Target
c.Formula = UCase(c.Formula)
Next c
' end
End Sub
[/pre]
-----


Regards!
 
SirJB7,

This code nearly worked .But i'm getting the message as:


Run time error ‘-2147417848(80010108)’:

Method ‘Formula’ of object ‘Range” failed.


and when I Debugged, the following line of the code got highlighted in the editor


c.Formula = UCase(c.Formula)


Please help


Thank you
 
[pre]
Code: 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
For Each Tc In Target
If Not ((Tc.Locked And ActiveWorkbook.ActiveSheet.ProtectContents) Or _
Tc.HasFormula) Then Tc.Value = UCase(Tc)
Next
End Sub
[/pre]
 
Hi, Ajit!

Could you get it working with Marc L's modification?

Regards!


@Marc L

Hi!

Thanks for the follow up.

Regards!
 
Watch out! Forgot to disable events...you'll get caught in an endless loop once the code starts making changes to the sheet. =O

[pre]
Code: 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.EnableEvents = False
For Each Tc In Target
If Not ((Tc.Locked And Sh.ProtectContents) Or _
Tc.HasFormula) Then Tc.Value = UCase(Tc)
Next
Application.EnableEvents = True
End Sub
[/pre]
 
Ajit

my humble contribution

[pre]
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Target = UCase(Target)
End Sub
[/pre]
 
@b(ut)ob(ut)hc

Hi!

Good afternoon, old dog new tricked and ninjaed.

I'm afraid it doesn't work for targets with more than one cell, so when copying text it won't do the job always.

Regards!
 
Good evening SirJB7


I have noticed over time that when I post a "solution" to a question/post up you pop with a reply that sinks my heart and sets out my next course of home work/study,.....but I will do your set home work and "try" to give a more workable solution...pass the malt :)
 
@b(ut)ob(ut)hc

Hi, my old english sheepdog!

It was Luke M who told me to do that... please check his previous comment. And as he's an older ancient antique long-standing Excel Ninja I obey.

Regards!

PS: I ran out of Carlsberg yesterday, I could only find Buds today so I'm drinking water... I think that this has a negative influence today, it's the 61nd time it happens.

PS2: BTW, one of the best practices (3nx green sticky post at this forums main page, paragraph 19) says "Check your answers before posting a solution.", thing that I don't always do, so welcome to this club too.
 
SirJB7

I realise you must be under some sort of pressure and with no Carlsberg you are clutching at straws and drinking Buds,....but what next.......Fosters, we use that instead of water to make beef puddings :) (no offense Hui)
 
LoL for my own forgot, thanks Luke and sorry Ajit for any problem ...

(was just a quick copy and paste from an own procedure but not in an event

any Carlsberg was injuried during this copy !)
 
@Marc L

Hi!

Carlsbergs never get injuried, it's forbidden for all physic and divine laws.

Regards!
 
Hi,


Thank you for the script. I found it very usefull.


However...


I need to modify this so that the automatic change in case only applies to (B2:Y8) and not the entire spreadsheet. Can this be done?
 
You must log in or register to reply here.
Back
Top