Forum Discussion
How to capitalize a specific word in a set of words?
Can someone explain to me or give me a trick on how to capitalize (or change to upper case) only a specific words in a particular constant position in an excel column?
I have a column list containing full names of people and I want to capitalize just the FIRST NAMES, while the other names and surnames remain untouched.
I've searched and searched online but can't find a real solution. The only functions I know of are the UPPER, LOWER and PROPER, LEFT, RIGHT and MlD which can only be used for strings of letters in a defined position &/length, whereas what I want to do are names with different number of letters.
So is there anyone here who can help me out?
Excel? @anybody??
Kcryptonian Perhaps something like this?
=UPPER(LEFT(A1,FIND(" ",A1)-1))&RIGHT(A1,LEN(A1)-FIND(" ",A1)+1)or
=UPPER(TEXTBEFORE(A1," "))&" "&TEXTAFTER(A1," ")...... depending on which Excel version you have. And there are probably a dozen more ways to do this.
6 Replies
- Thanks Harun, but the formular doesn't work on Excel 2010.
Kcryptonian Perhaps something like this?
=UPPER(LEFT(A1,FIND(" ",A1)-1))&RIGHT(A1,LEN(A1)-FIND(" ",A1)+1)or
=UPPER(TEXTBEFORE(A1," "))&" "&TEXTAFTER(A1," ")...... depending on which Excel version you have. And there are probably a dozen more ways to do this.
Didn't work out for me. I got the "#NAME?" error. I'm using excel version 2010.
I guess the formula doesn't work on it?
Kcryptonian The first formula should work but you haven't really revealed how your data looks like and how you are applying the formula.
- Thank you Riny_van_Eekelen. Let me go on and try it.
