All UPPER CASE to Upper & Lower Case

[Guest]

I actually have 2 questions
1--What is the formula to convert all upper case text to upper case (first letter) and lower case text
2--What is the formula to split the text contents of a cell into 2 cells & retain the text. i.e., convert one cell with "last name, first name" to 2 cells-one with "last name" and one with "first name"
I work with a lot of csv spreadsheets and need to change the information for better methods of sorting.

 

[DDM]

j9, can't help you if you're looking for a VBA solution; if you are, please
post back and I'm sure someone will reply. Here are the non-VBA solutions:

1-The PROPER worksheet function will convert all upper to initial upper. If
the data is in A1, insert a helper column and enter =PROPER(A1). Then copy
down. Select all values in helper column and Edit > Copy > Edit > Paste
Special > Values > OK. Then delete the original column.

2-The easiest way to convert "LastName, FirstName" is to use the Text to
Columns command on the Data menu. Check it out; it's very intuitive.
Alternatively, you can insert two helper columns and use the LEFT and MID
worksheet functions, something like this:

=LEFT(A1,FIND(",",A1)-1) for the last name,
=MID(A1,FIND(",",A1)+1,255) for the first.

Hope this answers your purpose.
--
DDM
"DDM's Microsoft Office Tips and Tricks"
Visit us at www.ddmcomputing.com

I actually have 2 questions:
1--What is the formula to convert all upper case text to upper case (first letter) and lower case text?
2--What is the formula to split the text contents of a cell into 2 cells &

retain the text. i.e., convert one cell with "last name, first name" to 2
cells-one with "last name" and one with "first name"?

I work with a lot of csv spreadsheets and need to change the information

for better methods of sorting.

 

[Guest]

Wohoo!

It works!! Both of them. Thanks a million!! I can go in on Monday and look like a real computer whiz thanks to you. I'll definitely tell the office what a good group this is for answers

I'll look more carefully at all the functions from here on out. Thanks again. You're the best!

j9

 

[Peo Sjoblom]

If you do this on a regular base you might want to use a macro to convert
upper text
to proper

Sub PropCase()
Application.DisplayAlerts = False
Dim R As Range
For Each R In Selection.Cells
If R.HasFormula Then
R.Formula = "=PROPER(" & Mid(R.Formula, 2) & ")"
Else
R.Value = Application.Proper(R.Value)
End If
Next
Application.DisplayAlerts = True
End Sub

press alt + f11 to open the VBE, click insert>module and paste in the above.
press alt + Q to close the VBE, now select the text, then press alt + f8 and
run the macro

--

Regards,

Peo Sjoblom

Wohoo!!

It works!! Both of them. Thanks a million!! I can go in on Monday and look

like a real computer whiz thanks to you. I'll definitely tell the office
what a good group this is for answers.