CODEWITHSUNDEEP
excelexcel-formula
david_10001
david_10001

Reputation: 492

Excel formula to make the first letter of each word a capital, without changing the remainder of the word

I would like to correct address entries in a workbook by ensuring all the terms use capital letters, however I can't use PROPER as it will also make the remainder of the terms lowercase.

For example, if someone enters 221B baker St, spring hill QLD, Australia, I would like to change this to 221 Baker St, Spring Hill QLD, Australia.

If I use the PROPER function for this, QLD is changed to Qld, which is incorrect.

So far I have written a formula to check if a cell is already in uppercase, and if it is, then return that cell value as it is. However, if the cell is not in uppercase, then use the PROPER function on it.

=IF(EXACT(UPPER(A1),A1),A1,PROPER(A1))

I would like to use this formula, however apply it to each word individually rather than the entire cell. I am not able to use VBA for this.

Update: I have managed to this this, however the formula is not very elegant:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," a"," A")," b"," B")," c"," C")," d"," D")," e"," E")," f"," F")," g"," G")," h"," H")," j"," J")," k"," K")," l"," L")," m"," M")," n"," N")," o"," O")," p"," P")," q"," Q")," r"," R")," s"," S")," t"," T")," u"," U")," v"," V")," w"," W")," x"," X")," y"," Y")," z"," Z"),", a",", A"),", b",", B"),", C",", C"),", d",", D"),", e",", E"),", f",", F"),", g",", G"),", h",", H"),", j",", J"),", k",", K"),", l",", L"),", m",", M"),", n",", N"),", o",", O"),", p",", P"),", q",", Q"),", r",", R"),", s",", S"),", t",", T"),", u",", U"),", v",", V"),", w",", W"),", x",", X"),", y",", Y"),", z",", Z")

Upvotes: 0

Views: 222

Answers (3)

chris neilsen
chris neilsen

Reputation: 53166

This is a bit tricky without VBA.

You basically need to

  1. split the string into words
  2. replace the first character of each word with its upper case version
  3. reassemble the sentence

Step 1 can be achieved with FilterXML, using space as the delimiter (Note, this assumes every word is seperated with at least a space, and possibly other characters too)

=FILTERXML("<b><a>" & SUBSTITUTE(A1," ","</a><a>")&"</a></b>","//a")

Step 2 uses Replace, Upper and Left to process the first character of each word

=REPLACE(B1#,1,1,UPPER(LEFT(B1#,1)))

Step 3 uses TEXTJOIN to reassemble the sentance

=TEXTJOIN(" ",TRUE,C1#)

enter image description here

Putting it all together is a single formula

=TEXTJOIN(" ", TRUE,REPLACE(FILTERXML("<b><a>" & SUBSTITUTE(A1," ","</a><a>")&"</a></b>","//a"),1,1,UPPER(LEFT(FILTERXML("<b><a>" & SUBSTITUTE(A1," ","</a><a>")&"</a></b>","//a"),1))))

TEXTJOIN was added in version 2017. FILETERXML in version 2013

To make it work in version >= 2013 and < 2017, use CONCAT instead of TEXTJOIN, plus a few tweeks to make it work.

=TRIM(CONCAT(REPLACE(FILTERXML("<b><a>" & SUBSTITUTE(A1," ","</a><a>")&"</a></b>","//a"),1,1," "&UPPER(LEFT(FILTERXML("<b><a>" & SUBSTITUTE(A1," ","</a><a>")&"</a></b>","//a"),1)))))

Upvotes: 2

bosco_yip
bosco_yip

Reputation: 3802

Try this longer formula of which doesnot required TEXTJOIN or CONCAT function,

In B1, enter formula :

=REPLACE(A1,1,MATCH(2,INDEX(1/(CODE(MID(LEFT(A1,LOOKUP(99,FIND(" ",A1,ROW($1:$99)))),ROW($1:$99),1))>96),0)),PROPER(LEFT(A1,MATCH(2,INDEX(1/(CODE(MID(LEFT(A1,LOOKUP(99,FIND(" ",A1,ROW($1:$99)))),ROW($1:$99),1))>96),0)))))

enter image description here

Upvotes: 1

Variatus
Variatus

Reputation: 14383

I split your address into its components, using the comma as a element separator. This may not work on all shapes of addresses you get but the formulas below may serve as a base for a better solution yet to be worked out.

  1. A2 = 221B baker St, spring hill QLD, Australia
  2. B2 =IFERROR(TRIM(LEFT(A2,FIND(",",A2)-1)),A2)
  3. C2 =MID(LEFT(A2,FIND("^",SUBSTITUTE(A2,",","^",2))-1),FIND(",",A2)+1,100)
  4. D2 =TRIM(LEFT(C2,LEN(C2)-LEN(E2)))
  5. E2 =TRIM(MID(C2,FIND("^",SUBSTITUTE(C2," ","^",LEN(C2)-LEN(SUBSTITUTE(C2, " ", ""))))+1,100))
  6. F2 =TRIM(MID(A2,FIND("^",SUBSTITUTE(A2,",","^",2))+1,100))

=PROPER(B2) & ", " & PROPER(D2) & " " & UPPER(E2) & ", " & PROPER(F2) returns the desired result.

Upvotes: -1

Related Questions