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.
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,
Does this help?
Formula:Please Login or Register to view this content.
Rule 1: Never merge cells
Rule 2: See rule 1
"Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".
Hi & welcome to the board.
How about
=REPLACE(A2,2,1,UPPER(MID(A2,2,1)))
you probably need VBA to update the cell itself
what i would do
in a new column
put
=LEFT(A1,1)&UPPER(MID(A1,2,1))&(RIGHT(A1,1))
and copy down
then select the column
COPY
then select the original column
Paste Special Value
then you can delete the column with formula
bit messy
But avoids VBA
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
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
conditional format
formula
=AND(A1<>"",LEN(A1)<>VALUE(LEFT(C1,1)))
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)
how is Design 1 entered
Fix could be added to a different cell
or if a formula could also have design 1 added then it would also be able to add the Fix
I assume the Cond Frmt i posted worked OK
in column D
=IF(Len(A2)=3, "Design 1", "Design 1 Fix")
In column D the "design name" will change. I've attached an updated screenshot for reference. Is it possible to have all of the tasks in one formula in column b?
Screen Shot 2020-04-30 at 9.28.36 AM.png
Last edited by AliGW; 04-30-2020 at 11:40 AM. Reason: Please don't quote unnecessarily!
No, a formula can only affect the cell that it's in & it cannot change the colour of a cell.Is it possible to have all of the tasks in one formula in column b?
Thank you for explaining. How would I update column d to include " fix" at the end if it meets the below requirements?
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.
Should this be done using a new column to run the formula, Example using column e?
Last edited by AliGW; 04-30-2020 at 11:41 AM. Reason: Please don't quote unnecessarily!
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.
can the design be looked up and populated using a lookupI have a separate sheet that lists all the design names along with the style type.
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.
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
how do you know where to paste , no customer id
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks