Capitalize 2nd Letter Only

I'm trying to capitalize the 2nd letter only for a monogram. The text is always 3 letters only. Currently I'm doing this manually 1 by 1. Below is an example

Current:
aaa
bbb
ccc

Updated:
aAa
bBb
cCc

Any help is greatly appreciated.

Hi & welcome to the board.
How about
=REPLACE(A2,2,1,UPPER(MID(A2,2,1)))

Originally Posted by sweep

Hi,

Does this help?

Formula:

Please Login or Register  to view this content.

Thank you this is exactly what I was looking for.

Originally Posted by Fluff13

Hi & welcome to the board.
How about
=REPLACE(A2,2,1,UPPER(MID(A2,2,1)))

Thank you!

You're welcome & thanks for the feedback.

This formula is such a huge timesaver I wanted to see if I could expand on it and locate errors. Below is a scenario of an error

If the cell in column C = "3 Letters" & the cell in column A is equal to 3 letters, Column B would show the correct format (lower case, UPPER CASE, lower case).
Else if cell in column C = "3 Letters" & the cell in column A is not equal to 3 letters, Column B would fill in with a color.

Below is a screenshot:
Screen Shot 2020-04-29 at 11.15.10 AM.png

Originally Posted by etaf

conditional format
formula
=AND(A1<>"",LEN(A1)<>VALUE(LEFT(C1,1)))

Thank you very much.

I wasn't sure if I should start a new thread for this since it's a slightly different topic. I wanted to make sure what's been done so far isn't lost. Below is a screenshot of what the updated scenario would look like.

Currently setup:
If the cell in column C = "3 Letters" & the cell in column A is equal to 3 letters, Column B would show the correct format (lower case, UPPER CASE, lower case).
Else if cell in column C = "3 Letters" & the cell in column A is not equal to 3 letters, Column B would fill in with a color.

To Add:
If the cell in column C = "3 Letters" & the cell in column A is not equal to 3 letters, add " Fix" to the end of the cell in column D.

Screen Shot 2020-04-30 at 8.44.11 AM.png

In B2 copied down
=IF(AND(LEN(A2)=3,C2="3 Letters"),REPLACE(A2,2,1,UPPER(MID(A2,2,1))),A2)

Is it possible to have all of the tasks in one formula in column b?

No, a formula can only affect the cell that it's in & it cannot change the colour of a cell.

Originally Posted by etaf

Yes ,otherwise i think you need VBA
How is the "design" column obtained can Excel work it out from somewhere else - or is it inputted

For the design column, i'm manually pasting that and the monogram column from an order sheet. Column C uses vlookup to check what type of style the design is. I have a separate sheet that lists all the design names along with the style type.

I'd like to catch potential errors automatically instead of my current process going row by row. If I could add " fix" to the end of the design name on ones do not equal 3 letters in column a, I would know to use a 2nd version of that exact design.

Originally Posted by etaf

can the design be looked up and populated using a lookup
if so then a fix can work

I'm not 100% sure. I've been pasting the columns manually and cleaning them up. I'm using an order sheet for reference to match the monogram with the correct design the customer ordered. The column that tells me the monogram type is automatically pulled from the directory sheet I made to reference each design and the monogram type.

Originally Posted by etaf

maybe upload a sample sheet

if its possible to lookup the design type based on the monogram - then if its not 3 letters you can add fix to the design found

I created a sample sheet. On the order sheet that is where I am currently pasting the monogram and design style based on what the customer orders.