Email:      Pass:    Pass?
Advertisements


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



Similar Excel Video Tutorials

Helpful Excel Macros

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
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
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
Delete Only the Text from Cells
- This macro will delete all text from selected cells. This will not delete any other characters or numbers from any cell
Delete Entire Rows Based on Predefined Criteria (Text)
- This macro will allow you to specify certain criteria and then to delete rows based upon that criteria. You will choose

Similar Topics







Hello,
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:
Y1 =UPPER(A1)
Y2 =UPPER(A2)
...
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)?

Tks.


Hi everyone,

I was wandering if someone could help me put together a text function.

The problem is, I have a cell which is full of text. The text is all in UPPER case.

I want to convert this text so it reads correctly. i.e Capital letter at the beginning of a sentence.

Working example.

Text in cell A1 is this:
THE QUICK BROWN FOX JUMPED OVER. THE FENCE.

I want it to read:
The quick brown fox jumped over. The fence

Obviously i'm going to need to use nested FIND, UPPER, and the Lower functions, however i'm at a loss as to where to start.

The "Proper" function doesn't do what i want it to do

Could someone please help

Thanks!


Hi,

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

Timbo


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 have searched the forum but can't find the answer I am looking for.

I have an excel 2003 worksheet with a range of cells that I want all text to be formatted as upper case as the data is inputted, whether the caps lock key is on or not.

Can this be done?

I know how to change lower to upper after the input but I want it to be done automatically.

Thanks for any help.


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?

Code:

'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;

'=UPPER(A1)
'=PROPER(A1)

'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
    Else
        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, "OzGrid.com")
    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!!!


I'm trying to convert text from "upper case" to "lower case" It used to be so easy.
I've followed the instructions but if the text is on D1 and you enter the function in E1 how do you get it back into the cell it belongs in. The text covers more than one cell. I'd appreciate any help anyone can offer.

Thanks, Pat


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
Jase


Hi all,

I've a linked worksheet to 2 other worksheets. In these 2 sheets there is a section which has 6 lines to add text.

I essentially want the linked sheet to combine the 2 text sections without any overlapping.

I've been given a complex formula before in picking up text which invloves ROWS. Would this be useful to use. (See thread here)


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)


hi all,
i thinking about this. If is possible to create any info about freezing panes.

I mean, when is pan freezed, and rows are scrolled in lower part of table (upper data are not vissible) if is possible display/show any text about this (e.g. "The first line of table are not visible")...but only if is not the upper part of data visible..not always.

thank you


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?


=TEXT(EDATE(F4,1),"MMMM") & " BALANCE"


Thanks in advance.


Hey,
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.
Thanks.
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.

Best,
Sep

Hello ,

I have the following problem : I received a large table with all cells containing information on two lines ( using break line function alt+enter). Each cell contains a text in the upper part and number in the lower part. Since I cannot do calculation on the numbers , I would like to transform each line of this table in two lines , one containning the upper part of each cell and the other one containning the lower part.
Do you have any idea how to do it?

Thanks in advance for any advice.

Olivier

PS: Sorry for my poor english, I'm from belgium....


Hello,

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?

Cheers,

Nick


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