Change all caps to lower case except the first letter.

[Xirom431]

Hi,

How do I change everything in my B column from all caps to lower case except the first letter of each word. The B column contains VLOOKUP formula also. Thanks.

 

[VoG]

Try

=PROPER(B1)

 

[njimack]

=left(b2)&lower(right(b2,len(b2)-1))

EDIT - I thought you only wanted to capitalise the first letter in the cell. For the first of each word, use VOG's suggestions.

 

[Xirom431]

Hi All,

Column B is containing VLOOKUP formula so I can't put anything in the cell.

 

[VoG]

If this is the same lookup as earlier, try changing it to

=IF(ISNA(VLOOKUP(F3,'Device List'!E2:F814,2,FALSE)),"",PROPER(VLOOKUP(F3,'Device List'!E2:F814,2,FALSE)))

 

[Xirom431]

Is there a way I can change entire column that have info in the cells already?

 

[VoG]

Note that this will replace formulas with values.

Try selecting the range then running the macro

Sub ToProper()
Dim c As Range
For Each c In Selection
    c.Value = StrConv(c.Value, vbProperCase)
Next c
End Sub

 

[Xirom431]

Thanks VoG

 

[RandyD123]

Works perfectly VoG....Thanks

 

[Jerk24]

Good Afternoon

Using Excel 2013

How would the formula change if I wanted to only use Proper function if the cell contains more than 3 Characters??