Combine UPPER function with another formula

[SallyH]

I've looked online, and I'm only seeing instructions on how to capitalize names by using the UPPER function by itself. Is there any way to combine this function with another formula? I'm wanting to change a Last Name, First Name to First Name Last Name, and then by the way, also capitalize the name.  Thanks!

[#2]

Of course, you can wrap LOWER, UPPER or PROPER text case functions around CONCATENATE, TEXTJOIN, LEFT, RIGHT and other text functions.

For example, PROPER(LEFT(K2,FIND(" ",K2)-1) will extract all the leftmost Name from the Full Name and delivered in Proper case.

See the attached picture

[#3]

@SallyH  

Yes, it's possible. A recommendation, though, if you don't mind: have two cells, one with first name, one with last, and then you can CONCATENATE either way.

So that's the recommendation.

Assuming you have them now in one cell (a mistake, for exactly the reason you're finding out) you will need first to separate them into First Name, Last Name anyway. The attached spreadsheet does that. A warning: if some have middle names or middle initials, this will need to be modified.

Once you have the names separated, you can then use the CONCATENATE function, or, more simply write a formula as you'll see in this sample file, that uses the & to assemble the text in a different fashion

You already know how to use UPPER so I didn't include that.

But I'm serious about keeping the names as two separate cells as the far better way to design your data in the first place. You can always create an alphabetized list that way (just sort on the last name), and you can assemble them for printing in either direction. The mistake is locking yourself into one way of doing it, as you've discovered.

[#4]

@SallyH -

It looks like this is similar to your need; with a little modification of course https://www.extendoffice.com/documents/excel/824-excel-flip-first-last-name.html.

=UPPER(REPLACE(MID(B2&", "&B2,FIND(",",B2)+2,LEN(B2)),FIND(",",MID(B2&", "&B2,FIND(",",B2)+2,LEN(B2))),1," "))

 

[#5]

@ChrisMendoza, @mathetes , @Abiola1 Thanks so much for the fast responses!! I had this formula: MID(B1&" "&B1,FIND(", ",B1)+1,LEN(B1))), which worked great for changing the names, but I couldn't figure out where to put the UPPER function. I kept wanting to show it as UPPER(B1). I finally found it online before I checked my messages here!  

UPPER(MID(B1&" "&B1,FIND(", ",B1)+1,LEN(B1))).  

[#6]

Absolutely... All Text Case Functions such as PROPER, LOWER and UPPER are always outside the formula i.e UPPER(CONCAT(A1," ",B1))

[#7]

Help, I can't figure out what is wrong with my formula.  I am trying to combine first and last names ( in seperate columns) and trying to make them upper case.  This is my formula and excel is telling me I am missing an opening or closing parenthesis, but where?=PROPER(UPPER(_xlfn.CONCAT(B10,C10))

  @mathetes 

[#8]

@mdan5678 

Decades ago I learned to locate missing parentheses by counting a formula from left to right, starting at zero, adding one for each left (or "opening") parenthesis, subtracting 1 each time I came to a right (or "closing") parenthesis....Usually that helps. In your case, you need a closing parenthesis at the very end.

Another trick that can work--if the formula gets really long--is to build the levels of nesting one by one, so you very consciously are adding an enclosing set of parentheses one pair at a time, for each new "outer" function.

HOWEVER, even when it works, that formula is not going to deliver the combined names all in caps. And the combination, when it works, isn't going to be what you want.

So let's start with the concatenation itself, where you're going to want to add a space between the two cells being concatenated. =CONCAT(B10," ",C10)

Without the space, you'll get johnsmith instead of john smith

Then, decide what you want:

=UPPER(CONCAT(B10," ",C10)) will yield JOHN SMITH

=PROPER(CONCAT(B10," ",C10)) will yield John Smith

You were adding an extra layer in your formula to begin with, in that UPPER and PROPER each do their own thing with a text string, and they don't really work together. The one that's on the 'outside' of the nesting levels "wins"

As a P.S., that "_xlfn." in your posted formula suggests that you may need to use CONCATENATE instead of CONCAT. You may be working with an older version of Excel, so if those suggested solutions don't work either, substitute CONCATENATE for CONCAT. 

And also consider updating your software if that's the case.