+ Reply to Thread
Results 1 to 23 of 23

Capitalize 2nd Letter Only

  1. #1
    Registered User
    Join Date
    04-29-2020
    Location
    US
    MS-Off Ver
    Not Sure
    Posts
    16

    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.

  2. #2
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016
    Posts
    3,303

    Re: Capitalize 2nd Letter Only

    Hi,

    Does this help?

    Formula: copy to clipboard
    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".

  3. #3
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    9,214

    Re: Capitalize 2nd Letter Only

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

  4. #4
    Registered User
    Join Date
    04-29-2020
    Location
    US
    MS-Off Ver
    Not Sure
    Posts
    16

    Re: Capitalize 2nd Letter Only

    Quote Originally Posted by sweep View Post
    Hi,

    Does this help?

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Thank you this is exactly what I was looking for.

  5. #5
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX, 365 for windows, Windows 10 Insider
    Posts
    6,300

    Re: Capitalize 2nd Letter Only

    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.

  6. #6
    Registered User
    Join Date
    04-29-2020
    Location
    US
    MS-Off Ver
    Not Sure
    Posts
    16

    Re: Capitalize 2nd Letter Only

    Quote Originally Posted by Fluff13 View Post
    Hi & welcome to the board.
    How about
    =REPLACE(A2,2,1,UPPER(MID(A2,2,1)))
    Thank you!

  7. #7
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    9,214

    Re: Capitalize 2nd Letter Only

    You're welcome & thanks for the feedback.

  8. #8
    Registered User
    Join Date
    04-29-2020
    Location
    US
    MS-Off Ver
    Not Sure
    Posts
    16

    Re: Capitalize 2nd Letter Only

    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

  9. #9
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX, 365 for windows, Windows 10 Insider
    Posts
    6,300

    Re: Capitalize 2nd Letter Only

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

  10. #10
    Registered User
    Join Date
    04-29-2020
    Location
    US
    MS-Off Ver
    Not Sure
    Posts
    16

    Re: Capitalize 2nd Letter Only

    Quote Originally Posted by etaf View Post
    conditional format
    formula
    =AND(A1<>"",LEN(A1)<>VALUE(LEFT(C1,1)))
    Thank you very much.

  11. #11
    Registered User
    Join Date
    04-29-2020
    Location
    US
    MS-Off Ver
    Not Sure
    Posts
    16

    Re: Capitalize 2nd Letter Only

    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

  12. #12
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    9,214

    Re: Capitalize 2nd Letter Only

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

  13. #13
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX, 365 for windows, Windows 10 Insider
    Posts
    6,300

    Re: Capitalize 2nd Letter Only

    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")

  14. #14
    Registered User
    Join Date
    04-29-2020
    Location
    US
    MS-Off Ver
    Not Sure
    Posts
    16

    Re: Capitalize 2nd Letter Only

    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!

  15. #15
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    9,214

    Re: Capitalize 2nd Letter Only

    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.

  16. #16
    Registered User
    Join Date
    04-29-2020
    Location
    US
    MS-Off Ver
    Not Sure
    Posts
    16

    Re: Capitalize 2nd Letter Only

    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!

  17. #17
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX, 365 for windows, Windows 10 Insider
    Posts
    6,300

    Re: Capitalize 2nd Letter Only

    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

  18. #18
    Registered User
    Join Date
    04-29-2020
    Location
    US
    MS-Off Ver
    Not Sure
    Posts
    16

    Re: Capitalize 2nd Letter Only

    Quote Originally Posted by etaf View Post
    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.

  19. #19
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX, 365 for windows, Windows 10 Insider
    Posts
    6,300

    Re: Capitalize 2nd Letter Only

    I have a separate sheet that lists all the design names along with the style type.
    can the design be looked up and populated using a lookup
    if so then a fix can work

  20. #20
    Registered User
    Join Date
    04-29-2020
    Location
    US
    MS-Off Ver
    Not Sure
    Posts
    16

    Re: Capitalize 2nd Letter Only

    Quote Originally Posted by etaf View Post
    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.

  21. #21
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX, 365 for windows, Windows 10 Insider
    Posts
    6,300

    Re: Capitalize 2nd Letter Only

    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

  22. #22
    Registered User
    Join Date
    04-29-2020
    Location
    US
    MS-Off Ver
    Not Sure
    Posts
    16

    Re: Capitalize 2nd Letter Only

    Quote Originally Posted by etaf View Post
    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.
    Attached Files Attached Files

  23. #23
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX, 365 for windows, Windows 10 Insider
    Posts
    6,300

    Re: Capitalize 2nd Letter Only

    how do you know where to paste , no customer id

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] first letter is capitalize
    By nizar2016 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-18-2016, 11:16 AM
  2. Capitalize first letter of every word
    By Steve1960 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-17-2013, 04:41 PM
  3. Capitalize all first letter
    By CoachBarker in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-07-2013, 12:25 PM
  4. [SOLVED] Only Capitalize First Letter & Delete Last Name
    By zulhfreelancer in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 7
    Last Post: 12-11-2012, 02:16 AM
  5. [SOLVED] Capitalize first letter in a cell
    By oskie89 in forum Excel General
    Replies: 4
    Last Post: 07-31-2012, 06:48 PM
  6. Capitalize first letter
    By randall78 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-14-2012, 03:56 AM
  7. Capitalize first letter of every word
    By inspirone1505 in forum Excel General
    Replies: 5
    Last Post: 02-20-2007, 03:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1