Combining Vlookup and Change Sentence Case into one formula

[VBA learner ITG]

hi all,

I need your guidance please.

I have a simple vlookup formula which returns a value. But I need to change the returned value sentence case to capital letters for each word.

=IFERROR(VLOOKUP($C4,'MASTER PLOF'!$A:$C,2,FALSE)," ")

 

[jmacleary]

Hi there. Use the PROPER function wrapped around your existing formula like this:

Book1
	C
10
Sheet4

Cell Formulas
Range	Formula
C10	C10	=PROPER(IFERROR(VLOOKUP($C4,'MASTER PLOF'!$A:$C,2,FALSE)," "))

 

[VBA learner ITG]

Thank you, this is exactly what i needed

 

[VBA learner ITG]

Hi there. Use the PROPER function wrapped around your existing formula like this:

Book1
	C
10
Sheet4

Cell Formulas
Range	Formula
C10	C10	=PROPER(IFERROR(VLOOKUP($C4,'MASTER PLOF'!$A:$C,2,FALSE)," "))

Hi jmacleary,

Can I get your adivce again as the "PROPER" formula has worked on majority of cells but I am finding where there are words such as: Goodfella's Thin its changing the letter after the Apostrophe to a capital letter for example: Goodfella'S Thin.

Is there a way around this issue?

 

[VBA learner ITG]

Apologies, I should have explained myself clearer.

I have amended the formula to factor in this "'S" issue but is there a way to capture all instances where there is an Apostrophe?

Current new formula below.

=SUBSTITUTE(PROPER(IFERROR(VLOOKUP($C4,'MASTER PLOF'!$A:$C,2,FALSE)," ")),"'S","'s")

 

[jmacleary]

Hi there. You have to trick the PROPER function into ignoring the apostrophe, then replacing it. Try this, which works unless any of your sentences has 'qzx' in:
=SUBSTITUTE(PROPER(SUBSTITUTE( IFERROR(VLOOKUP($C4,'MASTER PLOF'!$A:$C,2,FALSE)," ") ,"'","qzx")),"qzx","'")

 

[VBA learner ITG]

Hi there. You have to trick the PROPER function into ignoring the apostrophe, then replacing it. Try this, which works unless any of your sentences has 'qzx' in:
=SUBSTITUTE(PROPER(SUBSTITUTE( IFERROR(VLOOKUP($C4,'MASTER PLOF'!$A:$C,2,FALSE)," ") ,"'","qzx")),"qzx","'")

Thank you for your time. I have learnt something new today.

 

[jmacleary]

You're welcome and thanks for the feedback. That double substitute trick can be used in a number of ways to solve other similar problems.