How do I Convert Uppercase to Lowercase?

[techyguy717]

Software used, Office 2010.
My client works with excel spreadsheets, made by their headquarters.

The issue is that the values come in as such:

FIRST NAME, LAST NAME
1111 ADDRESS
CITY, STATE

They need the values as such:

First Name, Last Name
1111 Address
City, State

Headquarters is unwilling to change how they type them up.
Meanwhile my client has allocated resources to start typing them in ALL OVER AGAIN.
How do we get the values in lowercase, but with the first letter in uppercase?
I hope to be able to teach office workers an easy way to do this.

 

[mraikes]

Something like this?
http://misterexcel.wordpress.com/2008/06/24/change-uppercase-to-title-case/

 

[codegreen]

I would script it using VBA. I'm not an expert at VBA scripting, so I won't make a fool of myself offering code samples. This site might help: http://www.ozgrid.com/VBA/change-case-text.htm.

 

[ajm]

=proper(cell value or ranges)

 

['putertutor]

abbasmuraj beat me to it. proper() is probably the easiest way to get that done.

 

[altrenda]

As previously said, the proper function is built into excel.
Here is what I send out when asked this question.

http://www.cornerstonesolutions.com/help/Excel_Changing_Data_to_Proper_Case.pdf

 

[techyguy717]

Thank you all, this works great. Just one problem still remains.
Do I need to change the value for every row?
=Proper(A2)
=Proper(A3)
=Proper(A4)

They have thousands of rows and this will still be very time consuming.

 

[altrenda]

Thank you all, this works great. Just one problem still remains.
Do I need to change the value for every row?
=Proper(A2)
=Proper(A3)
=Proper(A4)

They have thousands of rows and this will still be very time consuming.

From the page I linked.

"3. Copy the formula down to apply it to the remaining rows in that column, now the new column is a proper case copy of the original data."

You do know how to copy formulas in Excel?

 

[Boston Pro Comp Serv LLC]

You can probably use a range = proper(A1,C3) or somthing to that effect

 

[techyguy717]

Thank you, altrenda and everyone else. This saved a lot of time.

I was copying the formula, but was not pasting the formula correctly.
I merely pasted.

After copying the formula I need to:

- Paste Special
- Paste as Formula

 

[KompuKare]

Thank you, altrenda and everyone else. This saved a lot of time.

I was copying the formula, but was not pasting the formula correctly.
I merely pasted.

After copying the formula I need to:

- Paste Special
- Paste as Formula

No you don't: you fill down CRTL-D (or right, CTRL-R). Alternatively you could copy everything to clipboard, paste into Word, get it to toggle case (Word calls it TitleCase) and paste special the values back in. You'd lose any formulas though. If all the offending UPPERCASE stuff is confined to whole rows or columns you can just copy those though.

If doing this a lot, VBA is the way to go though.

 

[altrenda]

I am not a VBA guy, but how would you do it that is easier than
Enter the formula in the first cell
Select the cells in the column you want to change
Click Home Fill Down on the ribbon (assuming excel 2010)

 

[techyguy717]

Correct Name Order. First, MI, Last.

Thank You,

I haven't been able to correctly line up the names though.

"Child, A, Julia" would become "A, Julia Child".
But I need it to be "Julia, A, Child"

Using this formula.
=RIGHT(A3,LEN(A3)-FIND(" ",A3,1))&" "&LEFT(A3,FIND(" ",A3,1))

I'm not sure how to correct this.

 

[coreyspeed]

Fomula would be

=LEFT(A3,FIND(" ",A3,1))&" "&RIGHT(A3,LEN(A3)-FIND(" ",A3,1))

 

[techyguy717]

coreyspeed, I can't get this formula to work.
=LEFT(A3,FIND(" ",A3,1))&" "&RIGHT(A3,LEN(A3)-FIND(" ",A3,1))

I still get this value: Child, A, Julia

When I switch the LEFT and RIGHT. I get this: julia child, A,

I'm trying to get Julia, A, Child.

I'm hoping to use this formula for a few hundred names.

 

[KompuKare]

This is pretty basic stuff, but anyway here goes:

=RIGHT(A3,LEN(A3)-FIND(",",A3,(FIND(",",A3))+1)-1)&", " & MID(A3,FIND(",",A3)+2,FIND(",",A3,FIND(",",A3,1))-5)&"," &LEFT(A3,FIND(" ",A3,1)-2)

That should do it.