Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Upper / Fill Series Function in Excel
Ok, I understand that in Excel, to convert a string of texts to all caps
could be done by using the UPPER formula ( e.g. UPPER("Text") ) My supervisor came to me and asked me, she has a column full of street addresses in mixed cases that she needs to convert to all caps. Example: 123 Main Street 1412 south avenue 592 northern main drive 243 EAST ROAD I tried the UPPER formula on the first one and wanted to do the same for all the others, but the Fill series / paste special all seemed to end up copying the contents of the first cell rather than repeating the =UPPER("text") for the series. Then I thought about doing this by reference. So I copie generated a seperate column and used the reference formula (=UPPER(A1)) and filled series, this worked. But my boss complained and said this is too complicated and insisted that there is an easier way. My question now is, is there any way to do this without having to use reference? Thanks a bunch. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Upper / Fill Series Function in Excel
=UPPER(A1)
Select that, hold the shift and down arrow and release these keys when you get to the end of your range. Then CTRL+D. The method you were trying is for data series, i.e., Monday, Tuesday, Wednesday, etc. Dave -- Brevity is the soul of wit. "Shohoku79" wrote: Ok, I understand that in Excel, to convert a string of texts to all caps could be done by using the UPPER formula ( e.g. UPPER("Text") ) My supervisor came to me and asked me, she has a column full of street addresses in mixed cases that she needs to convert to all caps. Example: 123 Main Street 1412 south avenue 592 northern main drive 243 EAST ROAD I tried the UPPER formula on the first one and wanted to do the same for all the others, but the Fill series / paste special all seemed to end up copying the contents of the first cell rather than repeating the =UPPER("text") for the series. Then I thought about doing this by reference. So I copie generated a seperate column and used the reference formula (=UPPER(A1)) and filled series, this worked. But my boss complained and said this is too complicated and insisted that there is an easier way. My question now is, is there any way to do this without having to use reference? Thanks a bunch. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Upper / Fill Series Function in Excel
Another thing to consider is that if your boss doesn't want to see the
formulas, just copy the column, select paste special--values and click OK. Dave -- Brevity is the soul of wit. "Dave F" wrote: =UPPER(A1) Select that, hold the shift and down arrow and release these keys when you get to the end of your range. Then CTRL+D. The method you were trying is for data series, i.e., Monday, Tuesday, Wednesday, etc. Dave -- Brevity is the soul of wit. "Shohoku79" wrote: Ok, I understand that in Excel, to convert a string of texts to all caps could be done by using the UPPER formula ( e.g. UPPER("Text") ) My supervisor came to me and asked me, she has a column full of street addresses in mixed cases that she needs to convert to all caps. Example: 123 Main Street 1412 south avenue 592 northern main drive 243 EAST ROAD I tried the UPPER formula on the first one and wanted to do the same for all the others, but the Fill series / paste special all seemed to end up copying the contents of the first cell rather than repeating the =UPPER("text") for the series. Then I thought about doing this by reference. So I copie generated a seperate column and used the reference formula (=UPPER(A1)) and filled series, this worked. But my boss complained and said this is too complicated and insisted that there is an easier way. My question now is, is there any way to do this without having to use reference? Thanks a bunch. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Upper / Fill Series Function in Excel
Thanks Dave for your response.
Well, the method you provided is what I already tried. That is, if the addresses were in column A, and in Column B, I reference the column using =UPPER(A1) and drag down to fill series, then it will work. What my boss wanted was finding out if there was a way to: First Do =UPPER("123 Main Street") and then using the fill function so that the formula format =UPPER("text") will be applied to subsequent cells automatically. =UPPER("1412 south avenue") =UPPER("592 northern main drive") =UPPER("243 EAST ROAD") I thought I could get by with using the reference column, but she wants a simple way where the average users will know how to do this. Simplest way would've been a built-in excel function that will change all highlighted cell contents to either UPPER/LOWER/PROPER case. But I don't think there is that function, or is there...? "Dave F" wrote: Another thing to consider is that if your boss doesn't want to see the formulas, just copy the column, select paste special--values and click OK. Dave -- Brevity is the soul of wit. "Dave F" wrote: =UPPER(A1) Select that, hold the shift and down arrow and release these keys when you get to the end of your range. Then CTRL+D. The method you were trying is for data series, i.e., Monday, Tuesday, Wednesday, etc. Dave -- Brevity is the soul of wit. "Shohoku79" wrote: Ok, I understand that in Excel, to convert a string of texts to all caps could be done by using the UPPER formula ( e.g. UPPER("Text") ) My supervisor came to me and asked me, she has a column full of street addresses in mixed cases that she needs to convert to all caps. Example: 123 Main Street 1412 south avenue 592 northern main drive 243 EAST ROAD I tried the UPPER formula on the first one and wanted to do the same for all the others, but the Fill series / paste special all seemed to end up copying the contents of the first cell rather than repeating the =UPPER("text") for the series. Then I thought about doing this by reference. So I copie generated a seperate column and used the reference formula (=UPPER(A1)) and filled series, this worked. But my boss complained and said this is too complicated and insisted that there is an easier way. My question now is, is there any way to do this without having to use reference? Thanks a bunch. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Upper / Fill Series Function in Excel
Using Worksheet Function UPPER there is no easier way than =UPPER(A1) and
drag/copy or fill series. The easiest way is to run a macro on a selected range to change to UPPER all at once with no formulas involved. Sub optUpper_Click() 'David McRitchie, programming, 2003-03-07 Dim rng1 As Range, rng2 As Range, bigrange As Range Dim cell As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual On Error Resume Next Set rng1 = Intersect(Selection, _ Selection.SpecialCells(xlCellTypeConstants)) Set rng2 = Intersect(Selection, _ Selection.SpecialCells(xlCellTypeFormulas)) On Error GoTo 0 If rng1 Is Nothing Then Set bigrange = rng2 ElseIf rng2 Is Nothing Then Set bigrange = rng1 Else Set bigrange = Union(rng1, rng2) End If If bigrange Is Nothing Then MsgBox "All cells in range are EMPTY" GoTo done End If For Each cell In bigrange cell.Formula = UCase(cell.Formula) Next cell done: Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub Your boss should know how to install and implement this code since she/he insists there is an easier way. Gord Dibben MS Excel MVP On Tue, 5 Sep 2006 10:31:03 -0700, Shohoku79 wrote: Ok, I understand that in Excel, to convert a string of texts to all caps could be done by using the UPPER formula ( e.g. UPPER("Text") ) My supervisor came to me and asked me, she has a column full of street addresses in mixed cases that she needs to convert to all caps. Example: 123 Main Street 1412 south avenue 592 northern main drive 243 EAST ROAD I tried the UPPER formula on the first one and wanted to do the same for all the others, but the Fill series / paste special all seemed to end up copying the contents of the first cell rather than repeating the =UPPER("text") for the series. Then I thought about doing this by reference. So I copie generated a seperate column and used the reference formula (=UPPER(A1)) and filled series, this worked. But my boss complained and said this is too complicated and insisted that there is an easier way. My question now is, is there any way to do this without having to use reference? Thanks a bunch. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Upper / Fill Series Function in Excel
Ha.
-- Brevity is the soul of wit. "Gord Dibben" wrote: Using Worksheet Function UPPER there is no easier way than =UPPER(A1) and drag/copy or fill series. The easiest way is to run a macro on a selected range to change to UPPER all at once with no formulas involved. Sub optUpper_Click() 'David McRitchie, programming, 2003-03-07 Dim rng1 As Range, rng2 As Range, bigrange As Range Dim cell As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual On Error Resume Next Set rng1 = Intersect(Selection, _ Selection.SpecialCells(xlCellTypeConstants)) Set rng2 = Intersect(Selection, _ Selection.SpecialCells(xlCellTypeFormulas)) On Error GoTo 0 If rng1 Is Nothing Then Set bigrange = rng2 ElseIf rng2 Is Nothing Then Set bigrange = rng1 Else Set bigrange = Union(rng1, rng2) End If If bigrange Is Nothing Then MsgBox "All cells in range are EMPTY" GoTo done End If For Each cell In bigrange cell.Formula = UCase(cell.Formula) Next cell done: Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub Your boss should know how to install and implement this code since she/he insists there is an easier way. Gord Dibben MS Excel MVP On Tue, 5 Sep 2006 10:31:03 -0700, Shohoku79 wrote: Ok, I understand that in Excel, to convert a string of texts to all caps could be done by using the UPPER formula ( e.g. UPPER("Text") ) My supervisor came to me and asked me, she has a column full of street addresses in mixed cases that she needs to convert to all caps. Example: 123 Main Street 1412 south avenue 592 northern main drive 243 EAST ROAD I tried the UPPER formula on the first one and wanted to do the same for all the others, but the Fill series / paste special all seemed to end up copying the contents of the first cell rather than repeating the =UPPER("text") for the series. Then I thought about doing this by reference. So I copie generated a seperate column and used the reference formula (=UPPER(A1)) and filled series, this worked. But my boss complained and said this is too complicated and insisted that there is an easier way. My question now is, is there any way to do this without having to use reference? Thanks a bunch. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Upper / Fill Series Function in Excel
You'd have to create named ranges for each of those text strings, which is
not what the average user knows. Does your boss understand Excel? The way you've done it is the simplest way to do it. Dave -- Brevity is the soul of wit. "Shohoku79" wrote: Thanks Dave for your response. Well, the method you provided is what I already tried. That is, if the addresses were in column A, and in Column B, I reference the column using =UPPER(A1) and drag down to fill series, then it will work. What my boss wanted was finding out if there was a way to: First Do =UPPER("123 Main Street") and then using the fill function so that the formula format =UPPER("text") will be applied to subsequent cells automatically. =UPPER("1412 south avenue") =UPPER("592 northern main drive") =UPPER("243 EAST ROAD") I thought I could get by with using the reference column, but she wants a simple way where the average users will know how to do this. Simplest way would've been a built-in excel function that will change all highlighted cell contents to either UPPER/LOWER/PROPER case. But I don't think there is that function, or is there...? "Dave F" wrote: Another thing to consider is that if your boss doesn't want to see the formulas, just copy the column, select paste special--values and click OK. Dave -- Brevity is the soul of wit. "Dave F" wrote: =UPPER(A1) Select that, hold the shift and down arrow and release these keys when you get to the end of your range. Then CTRL+D. The method you were trying is for data series, i.e., Monday, Tuesday, Wednesday, etc. Dave -- Brevity is the soul of wit. "Shohoku79" wrote: Ok, I understand that in Excel, to convert a string of texts to all caps could be done by using the UPPER formula ( e.g. UPPER("Text") ) My supervisor came to me and asked me, she has a column full of street addresses in mixed cases that she needs to convert to all caps. Example: 123 Main Street 1412 south avenue 592 northern main drive 243 EAST ROAD I tried the UPPER formula on the first one and wanted to do the same for all the others, but the Fill series / paste special all seemed to end up copying the contents of the first cell rather than repeating the =UPPER("text") for the series. Then I thought about doing this by reference. So I copie generated a seperate column and used the reference formula (=UPPER(A1)) and filled series, this worked. But my boss complained and said this is too complicated and insisted that there is an easier way. My question now is, is there any way to do this without having to use reference? Thanks a bunch. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Upper / Fill Series Function in Excel
Thanks again for the help, Dave and Gord.... I did enjoy the slight hints of
sarcasm (even if it was not intended.) ;) Though the macro does exactly what was needed and more (e.g. capitallizes and even removes the formula). I think presenting that to the average user might be a bit extreme. Unless they don't mind having to import the macro every time they start a new workbook that might require this function.... I think it's easier for them to learn the referenced/drag & fill, and just hide/lock the referenced column if they don't want to see it. Thanks again to the both you. --------- "Dave F" wrote: Ha. -- Brevity is the soul of wit. "Gord Dibben" wrote: Using Worksheet Function UPPER there is no easier way than =UPPER(A1) and drag/copy or fill series. The easiest way is to run a macro on a selected range to change to UPPER all at once with no formulas involved. Sub optUpper_Click() 'David McRitchie, programming, 2003-03-07 Dim rng1 As Range, rng2 As Range, bigrange As Range Dim cell As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual On Error Resume Next Set rng1 = Intersect(Selection, _ Selection.SpecialCells(xlCellTypeConstants)) Set rng2 = Intersect(Selection, _ Selection.SpecialCells(xlCellTypeFormulas)) On Error GoTo 0 If rng1 Is Nothing Then Set bigrange = rng2 ElseIf rng2 Is Nothing Then Set bigrange = rng1 Else Set bigrange = Union(rng1, rng2) End If If bigrange Is Nothing Then MsgBox "All cells in range are EMPTY" GoTo done End If For Each cell In bigrange cell.Formula = UCase(cell.Formula) Next cell done: Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub Your boss should know how to install and implement this code since she/he insists there is an easier way. Gord Dibben MS Excel MVP On Tue, 5 Sep 2006 10:31:03 -0700, Shohoku79 wrote: Ok, I understand that in Excel, to convert a string of texts to all caps could be done by using the UPPER formula ( e.g. UPPER("Text") ) My supervisor came to me and asked me, she has a column full of street addresses in mixed cases that she needs to convert to all caps. Example: 123 Main Street 1412 south avenue 592 northern main drive 243 EAST ROAD I tried the UPPER formula on the first one and wanted to do the same for all the others, but the Fill series / paste special all seemed to end up copying the contents of the first cell rather than repeating the =UPPER("text") for the series. Then I thought about doing this by reference. So I copie generated a seperate column and used the reference formula (=UPPER(A1)) and filled series, this worked. But my boss complained and said this is too complicated and insisted that there is an easier way. My question now is, is there any way to do this without having to use reference? Thanks a bunch. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change the color of all series in an excel chart in one go. | Charts and Charting in Excel | |||
Converting Getpivotdata function in Excel '03 to Excel '00?? | Excel Worksheet Functions | |||
numerical integration | Excel Discussion (Misc queries) | |||
"Group" function very slow with Excel 2003 :( ... While very quick with Excel2000 :O) | Excel Discussion (Misc queries) | |||
How to change the color of all series in an excel chart in one go. | Charts and Charting in Excel |