# formula for upper & lower case in same column

Copper Contributor

# formula for upper & lower case in same column

I am trying to find a formula to change the last names to uppercase followed by a comma then proper case the first name, in the same column.

5 Replies

# Re: formula for upper & lower case in same column

Let's say you have a name such as

presti, christine

in A2. Enter the following formula in another cell in row 2:

=UPPER(LEFT(A2,FIND(",",A2)-1))&PROPER(MID(A2,FIND(",",A2),100))

Fill down as far as needed.

# Re: formula for upper & lower case in same column

A very slight variation on what my friend @HansVogelaar has written for you.

=UPPER(LEFT(A2,FIND(",",A2)))&PROPER(RIGHT(A2,LEN(A2)-FIND(",",A2)))

works as shown here

# Re: formula for upper & lower case in same column

In general, however, I would avoid putting first and last names into the same cell. Why? Doing that limits flexibility. Keeping them separate allows you to assemble such things as alphabetical listings sorted by last name AND also create name labels for various purposes with first name first.

# Re: formula for upper & lower case in same column

Thank you so much!@HansVogelaar

# Re: formula for upper & lower case in same column

Thank you so much!