Capitals to Lower Case

[jo.stanley]

Hi

I know its a cheek but I hve two questions really.

Is there a quick way of formatting data in Excel from Caps to lower case

i.e.

from LOWER to Lower

I have 15000 cells with data all in CAPS and i need and easy way to modify

ALSO

Is there an easy way to merge data from Three cells into 1 cell

i.e.

Mr John Smith

 

[tmischler]

=lower()

and

format / cells / alignment / merge cells

 

[stuartford]

=proper(a1)
will change from upper case to proper case if thats any good

Stu

 

[jo.stanley]

Hi

thanks for that

but if I merge like this i lose the data from two of the cells

 

[tmischler]

oh - try = concatenate() or = a1&a2&a3

 

[stuartford]

u could just do
=a1&b1&c1
this will work but there must be another way

Stu

 

[jo.stanley]

Thank you - from a novice

 

[tmischler]

Try assigning this macro to a button

Sub test()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim myrange As Range
Dim domicile As String
Static myText As String

For Each myrange In Selection

domicile = myrange.Address
myText = myText & Range(domicile).Value

Next myrange

ActiveCell.Value = myText
Selection.MergeCells = True

myText = ""

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[TIM WEIPERS]

Hi Jo,

If you download the amazing "ASAP Utilities" from the MrExcel tutorials and articles page both of your problems will be solved with the click of the mouse.