Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

Upper Text Function

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I want to change Normal Text on one worksheet to CAPITAL TEXT on a second
sheet to which it is linked. I have been able to do this by adding an
additional column to my second sheet, using the "UPPER" function and copying
all the data to the adjacent column. Can I apply this funtion directly to my
linked text ? I get assorted error messages when I try this. The formula
looks somehting like :
=Summary!G10+Upper(b3) where the first part is the name of the linked data,
and the second part attempting to convert it to capitals!
Any advice please? Many Thanks

View Answers     

Similar Excel Tutorials

Make All Text Upper Case or Lower Case in Excel
How to quickly change all text to upper case or lower case in Excel.  This allows you to change the case of text wi ...
AND Function - Check if All Arguments are True
The AND function in Excel checks if every argument you put in it evaluates to TRUE.  If everything evaluates to TRU ...
Capitalize First Letter of Every Word in a Cell - PROPER Function
In Excel you can use a function to capitalize the first letter of every word in a cell.  This allows you to prepare ...
Count the Number of Cells that Contain Specific Text in Excel
How to count the number of cells that contain specific text within a spreadsheet in Excel. I'll show you how to cou ...

Helpful Excel Macros

Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
Macro to add a New Line to Message Box Pop-up Windows in Excel
- This is a very simple Message Box, pop-up window, macro for Excel that illustrates how to put new lines, the same thi
Convert Numeric Dollar Values into Text in Excel - UDF
- Convert a numeric dollar amount into its text equivalent. This UDF (user defined function) for Excel will change or tra
Filter Data in Excel to Display Records that Contain a Value Between Two Values - AutoFilter.
- This free Excel macro filters data to display only those records or rows that contain a value between two values. For e
Get Values from a Chart
- This macro will pull the values from a chart in excel and list those values on another spreadsheet. This will get the s

Similar Topics

I have an ordinary worksheet with some ordinary colums. Nothing so special. But, in one of them, I'd like to automatically convert its characters to uppercase.
I can't do this using the UPPER function because it requires a helping column to do it, I mean, if the column I want to change case is A, then I'd need to create, in column Y (assuming I have a big sheet), the following:
which would return the desired results in the undesired place. Is that possible to have the text typed inside column A automatically uppercased as I type (as I finish entering info in a cell)?



Is it possible to change and entire sheet to Upper case using the 'Upper' function?

We are need to change all the text to caps but can only seem to apply the function to one cell at a time and then copy it down for the column. This is going to be a fairly labour intensive process as we have many columns. Is there an easier way to achieve this.

Thanks in advance


I'm wanting to ensure that text entered by the user displays in upper case but I can't figure out how to do this. For example, if text is entered into cell A1, how can that text be changed to upper case?

I know that using the function UPPER() in some other cell will display the text in that cell in upper case but the text in A1 remains unchanged.

I got this macro from some NG some where (Thanks), what I need help on is making the macro ignore all characters in brackets "()".
I am absolutely dying trying to mod this macro.
Heck, is it even possible?


'Change Text to Upper Case or Proper Case. See Also:
'Force Upper Case/Proper Case

'Excel has 2 built in functions for converting text to
'either UPPER CASE or Proper Case. The 2 functions that
'do this are shown below;


'These Excel functions work well when referring to cells
'that house the text. However, there are many instances
'when using the Worksheet Function approach is not practical.
'The Excel macro code below can be used to change existing
'text to either UPPER CASE or Proper Case. If you run the
'macro with only a single cell selected it will work on the
'entire Worksheet. If you run the macro with more than 1
'cell selected it will work on only your selection.
'The other settings that the StrConv Function take are
'shown below. See the Excel VBA help for specifics.

Sub ConvertCase()
    Dim rAcells As Range, rLoopCells As Range
    Dim lReply As Long

    'Set variable to needed cells
    If Selection.Cells.Count = 1 Then
        Set rAcells = ActiveSheet.UsedRange
        Set rAcells = Selection
    End If

    On Error Resume Next    'In case of NO text constants.
    'Set variable to all text constants
    Set rAcells = rAcells.SpecialCells(xlCellTypeConstants, xlTextValues)

    If rAcells Is Nothing Then
        MsgBox "Could not find any text."
        On Error GoTo 0
        Exit Sub
    End If

    lReply = MsgBox("Select 'Yes' for UPPER CASE or 'No' for Proper Case.", _
                    vbYesNoCancel, "")
    If lReply = vbCancel Then Exit Sub

    If lReply = vbYes Then    ' Convert to Upper Case
        For Each rLoopCells In rAcells
            rLoopCells = StrConv(rLoopCells, vbUpperCase)
        Next rLoopCells
    Else    ' Convert to Proper Case
        For Each rLoopCells In rAcells
            rLoopCells = StrConv(rLoopCells, vbProperCase)
        Next rLoopCells
    End If

End Sub

As always, I value all help provided.

To the MOD who improved my title, Thanks!

Hi all,

with the help of Replace function, I'm replacing certain letters of a text (all the text is in column A) with certain symbols (eg. "a" is replaced by "!", etc.). But I have problem with capital letters, cause, for example, "S" and "s", are treated as same, so when I replace "S" with "?", "s" is replaced by same symbol. is their anyway to make excel "see" if letter is capitalized or not (I tried to put UPPER function in IF, but it didn't work).

Thanks in advance!!!

This is my first time ever programming with VBA and I figured that
creating a function that sums up the number of items that fit a couple
of criteria wouldn't be very hard. However, this is proving to be more
difficult for me than I thought. This is what I have, but it returns a
#VALUE! error in the hosting cell:

Function OtherINLDue(INL1() As String, Category1() As String, INL2() As
String, Category2() As String)

Dim i As Integer
Dim totalDue As Integer

For i = 0 To UBound(INL1)

If Category1(i) = "LN2" Or Category1(i) = "LN4" Then
If UPPER(Left(INL1(i), 3)) <> "EKT" And UPPER(Left(INL1(i), 2))
<> "NT" And UPPER(Left(INL1(i), 2)) <> "TM" And UPPER(Left(INL1(i), 2))
<> "FC" And UPPER(Left(INL1(i), 5)) <> "WFFOD" And UPPER(Left(INL1(i),
5)) <> "CEDDS" And UPPER(Left(INL1(i), 4)) <> "PROD" And
UPPER(Left(INL1(i), 2)) <> "SM" And UPPER(Left(INL1(i), 2)) <> "SS" And
UPPER(Left(INL1(i), 2)) <> "TS" Then
totalDue = totalDue + 1
End If

Next i

For i = 0 To UBound(INL2)

If Category2(i) = "LN2" Or Category2(i) = "LN4" Then
If UPPER(Left(INL2(i), 3)) <> "EKT" And UPPER(Left(INL2(i), 2))
<> "NT" And UPPER(Left(INL2(i), 2)) <> "TM" And UPPER(Left(INL2(i), 2))
<> "FC" And UPPER(Left(INL2(i), 5)) <> "WFFOD" And UPPER(Left(INL2(i),
5)) <> "CEDDS" And UPPER(Left(INL2(i), 4)) <> "PROD" And
UPPER(Left(INL2(i), 2)) <> "SM" And UPPER(Left(INL2(i), 2)) <> "SS" And
UPPER(Left(INL2(i), 2)) <> "TS" Then
totalDue = totalDue + 1
End If

Next i

OtherINLDue = totalDue

End Function

Any Help would be appreciated

Does anyone know how to convert all lower string text to be all in Upper case?

I now you need to use the UPPER function but how do I convert the whole document?

Thank you

I have a combobox embedded into an excel sheet with a linked cell and a range defined in the combobox properties. Every time I change the value in the combobox, the value in the linked cell is stored as text instead of a number. I have tried copying a cell containing 1 and copying and pasting as special into the linked cell but that only fixes it for the time being. If I change the value in the combobox again, the value in the linked cell is again stored as text. I tried =value() in the linked cell but that again only worked temporarily. Every time I change the value in the combobox, the value in the linked cell is stored as text. How is this fixed?

Ok before you say "Easy, just use the UPPER function" that's not not what I want.
If I type abc123 in cell A1 I want it to convert to ABC123 in A1 as soon as I hit enter. I don't think it is possible, but I thought I'd ask.
Thanks in advance

Sorry to ask such a simple question, but I'm having no luck in finding the answer myself.

I have a work sheet with a number of columns, all working well.

A 'minor' problem I have is that one column only has Text entry required, no problem, but I would like this Text to always be in Upper Case.
Ok, I can use the Shift key, but I'm no typist and I always seem to mess up and have to make corrections.

Is there a way please that I can enter Lower case or a mixture of Lower / Upper case letters that will always end up as Upper case in this particular column?

Thanks for your time.

magpye (John)

I want to capitalize the text that Excel displays in my formula. I know that I need to add "UPPER" somewhere in this formula, but where?


Thanks in advance.

This is probably really easy but its got me stumped.
I have a spread sheet where other users fill in a column but need this column to always display in capitals. I know the =UPPER() formula but that isnt right for what i need.
Any help is welcome.
Emma x

Hello world,

I have a workbook that has many mathematically linked cells, and I would like to add text to some of the cells. The only method I know of doing sois adding the below at the end of the equation of the cell I want to add text to:

> &CONCATENATE("words...texts...")

Works great, except the formulas of the rest of other cells that are linked this cell (that I just added text to) stop working since they need a number input (instead of a number and some text).

Is there a way I can work around this to make the other cells still be able to calculate their respective formula?

Or is there a different way I can add text to a cell that has a formula? Is there a way to make the next cell only use part of the value of the previous cell and ignore the text?

Thank you so much guys, I'm learning so much here.



Please see attached file.

What I'd like to do is search column a , and if finds anywhere in text an uppercase within text, display entire text, else display ""

I tried =IF(ISERROR(FIND(" ",A2)),EXACT(A2,UPPER(A2))) but that didnt work.

any help would be appreciated

Is it possible to format, say, a column to automatically convert text entered in that column to upper case, no matter how the text is entered?

That is, when the enter/tab is hit, the text will appear in upper case, no matter what the case when entered.

I am just trying to overcome having to remember to use the caps lock.

Hi there,

I have a spreadsheet that is downloaded from a network. The names in column C are in upper case and I want to use the "Proper" function to convert to upper and lower case.

Each time I use this function (in column D) it produces the words "=PROPER(C)". Column D is formatted as text. Does anyone know why this occurs?

Thank you.

Hi Guys,

Having a bit of trouble with Excel at the moment.
I have a field within a worksheet that needs to be Uppercase, as at the moment it is Proper case.

Everywhere I have looked for an answer has said use the =UPPER() command, but thats not what I am after!

I want to be able to select the field I want in uppercase, and apply that to the text.

Anyone any ideas?



Hello all who read this,

I have some text, below in Excel and I need to add in a comma and a space every time there is a letters in lowercase followed by a letter in upper case! Or when you see 'cP' I want to show as 'c, P'.

I have tried fiddling with formulas but cannot find a way to detect the lowercase letter followed by an upper case letter. I will add that as you will see from the text I have below I cannot use the text to columns function and the letters will be a random mix, oh and I have several hundred lines of text to apply this to!

Mr DeakinsHappily RoadWolverhampton RoadCodsallStafford
Harry SmithKinwarton Farm RoadArden ForestAlcesterWarwickshire
A CorneliusArden Forest EstateAlcesterWarwickshire
Mrs AirDonnington HallCastle DonningtonDerbyshire

Thanks to anyone who may help with this.

I have a data sheet with a mix of upper and lower case text in various fonts.
How do I change it to all upper case? Can change font & size ok. Thanks.
Regards John

I am attempting to change a range of cells from lower case text to all upper case text. I'd appreciate assistance in accomplishing this goal. Thanks.

Can anyone let me know how to check a cell for text in all upper case?

I have a column containing only text, some cells contain the
manufacturer & some the model. However the Manufacturer is in all upper
case. And the cells below that particular Manufacturer are the models of
that manufacturer.

So I am wanting to check for all upper case so I can get the related
manufacturer in the cell to the left of the model field, using an IF

What can I incorporate into an IF to check a cell for an all upper-case
text string?

Many TIA


i have cells with city and state in them and i'm trying to separate the one column into two columns. the problem is, i'm trying to keep the city name in one column and the state in the other. some of my cells have two word cities like new albany, or upper arlington. the text to columns feature is separating those cells into 3 columns not 2. is there a way to do this?

worthington, oh
upper arlington, oh

text to columns splitting upper arlington into 3 different cells because the only delimiter in the cell is a space. i need to keep upper arlington in one cell and oh in another. thanks for any help

I have a dozen links on an Index worksheet, but most of the linked name ranges is usually only an area of about 6 rows by 8 columns. I have several of these "arrays" on the same worksheet, one under another with a couple empty rows between them.

When I click the link, it goes to that named range, but I would like to always put that range in the upper left hand corner of the window, so they don't have to scroll to see some of it.

Thoughts ??


I am developing a tool where users enter data into a number of text boxes and combo boxes that are linked to cells on a hidden worksheet that manipulates the data and returns an output back.

The problem that I am facing is that with the way that the linked-cell function works in excel, it automatically refreshes the linked cell with each character change made inside the text box.

For example if I try to type in a filed like "employee id#" which might be 7 digits long, it will refresh the linked cell after every diget. The problem is that every time it refreshes, it recalculates all the formulas on the entire sheet (which takes a second or two.) This is very disruptive if you are trying to enter a 7 digit number and have to lag for two seconds in between each one.

Is there any way to stop Excel from doing this until the whole field has been filled in?

Thanks very much,

On a side note, does anyone know how to do a macro that will allow me to navigate between combo boxes using the Tab key?


I've read a handful of threads on here regarding combination, but my challenge is a bit different.

I have 9 characters, i know the order the need to appear in, however, i need the different upper and lower case combinations.

Here are the characters AND in the correct sequence:

upper or lower case "g"
upper or lower case "r"
upper or lower case "o"
upper or lower case "o"
upper or lower case "v"
upper or lower case "e"
upper or lower case "n"
upper or lower case "y"
upper or lower case "c"

Note that the "o" needs to be repeated in the string.

Does anyone know how to plug this into Excel with the appropriate formulas and get all the possible combinations?

Thank you in advance,