# Make 1st Letter Upper Case

#### erock24

##### Well-known Member
I have cells that have 1 or 2 words in them. Is there a formula I can use that will make the 1st letter of each word Upper Case. The whole range started out all caps. I used =lower(a1) to make them all lower. Now I just need 1st letter of each word upper.

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

=Proper(a1)

#### erock24

##### Well-known Member
cool.
Thank you very much.

#### erock24

##### Well-known Member
Is there a way to get around this exception to the rule?

my cell = JOHN MCDOE
=proper(a1) = John Mcdoe
what I need = John McDoe

the =proper formula works perfectly except for these "Mc" exceptions.
Is there a way around this.

#### dave3009

##### Well-known Member
Hi Erock

I know it is customary to post a solution but my brain has ceased operation at this point. I can get you to John Mc but am struggling with the bit that gives you Doe.

Hopefully you can utilise the first part of the formula to return the Doe bit

=IF(ISNUMBER(SEARCH("MC",A26)), PROPER(LEFT(A26,SEARCH("MC",A26)+1)), 0)

Also that 0 should be PROPER(A26)

Sorry I only got so far

HTH

Dave

#### Scott Huish

##### MrExcel MVP
Perhaps:
=TRIM(SUBSTITUTE(PROPER(SUBSTITUTE(UPPER(" " & A1)," MC"," MC "))," ",""))

#### facethegod

##### Well-known Member
Perhaps

Code:
``=IF(NOT(ISERROR(SEARCH(" mc",A1))),REPLACE(PROPER(K5),SEARCH(" mc",A1)+3,1,UPPER(MID(K5,SEARCH(" mc",A1)+3,1))),PROPER(A1))``

HTH

#### erock24

##### Well-known Member
Hotpepper,
works almost perfectly, It loses the space between the 1st and the last name.

Is there a fix for this?

Thank you for your time and help.

#### Scott Huish

##### MrExcel MVP
Sorry, that didn't show on the board correctly, I'll try it like this:

=TRIM(SUBSTITUTE(PROPER(SUBSTITUTE(UPPER(" " & A1)," MC"," MC "))," ",""))

There are supposed to be two spaces after the MC in the first dark blue MC and two spaces between the quotes in the second dark blue part.

#### erock24

##### Well-known Member
Hotpepper,

works perfectly. Thank you very much.

facethegod. Yours also works perfectly.

Thank you all very much for your time and help.

Replies
4
Views
340
Replies
3
Views
171
Replies
0
Views
343
Replies
4
Views
758
Replies
3
Views
2K

1,190,644
Messages
5,982,105
Members
439,755
Latest member
nicos18

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back