Format text in Excel

  • Thread starter Thread starter kez
  • Start date Start date
You need a small macro, or a formula in another cell, like
=LOWER(A1)

HTH. Best wishes Harald
 
If you have this in cell A1:
THIS IS MY TEXT
then enter this in cell B1:
=LOWER(A1)
and B1 will display
this is my text

If this is not what you want then please be more specific.

HTH. Best wishes Harald
 
I have 7000 addresses in my excel database, approximately 5000 are uppercase
text! I need to change them to lower case? is there a quick way without me
having to edit them all individually? Thanks
 
This does not work ?
Harald Staff said:
If you have this in cell A1:
THIS IS MY TEXT
then enter this in cell B1:
=LOWER(A1)
and B1 will display
this is my text

If this is not what you want then please be more specific.

HTH. Best wishes Harald
Click to expand...
 
Kez

Sub Lower_Case()
'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 = LCase(Cell.Formula)
Next Cell
done:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


Gord Dibben Excel MVP
 
I read this as a question about wether my suggestion works or not. Yes it
does, at least it works perfectly fine on my computer. I don't find
deliberate non-working sloutions amusing, so have no fear. Give it a try. I
come in peace, take me to your leader.
 
Kez

Copy and paste to a general module in your workbook.

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to Tool>Macro>Macros.

Gord Dibben Excel MVP
 
Back
Top