+ Reply to Thread
Results 1 to 5 of 5

Formula needs to compare *cases* (upper/lower) of FIRST LETTER and update words correctly

  1. #1
    Forum Contributor
    Join Date
    08-06-2018
    Location
    Virginia
    MS-Off Ver
    2019
    Posts
    333

    Formula needs to compare *cases* (upper/lower) of FIRST LETTER and update words correctly

    Hello:

    I need some assistance with updating the *case* (lower/upper) of a word's **first** letter.

    Background:
    - As part of standardizing job description's tenses, I have a VBA routine which updates a word in past tense from (e.g., "Acquired") to simple present tense (e.g., "Acquire").
    - This routine works fine; however, depending on previous word usages, a word's **first** letter may erroneously get changed from, e.g., "a" to "A" or vice versa.
    - Attached XLS contains a few sample words in column B. These words may be written in past tense, simple present tense, or simple present tense continuous. All of these are fine.
    - The issue lies in column C. Although the tenses are correct for all words, the 1st letter of the word in column C may be different than the 1st letter of column B (regardless of the tense).

    My goal:
    - I need to come up with an Excel formula with will "look" at the case of the 1st letter in column C and then compare it to the 1st letter of the word in column B.
    - If there is a difference (whether it's upper vs. lower OR lower vs. upper), then the 1st letter of the word in column C must be updated to the 1st letter of column B's word (while keeping column C's word in the same tense... i.e., simple present tense).

    Again, as a picture is worth a 1000s words, a look at the attached XLS should make the requirement clear. Does anyone know which formula (e.g., in column F) would update column C's 1st letter correctly?

    Thanks,
    EEH
    Last edited by skydivetom; 01-21-2022 at 08:49 PM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Formula needs to compare *cases* (upper/lower) of FIRST LETTER and update words correc

    I think it would be better to look at the macro routine and ensure that the case of the initial letter is not changed in that.

    Hope this helps.

    Pete

    Pete

  3. #3
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,329

    Re: Formula needs to compare *cases* (upper/lower) of FIRST LETTER and update words correc

    it is not clear why you need to compare. Replace first letter. =Replace(B2,1,1,Left(c2))
    to compare you can use =EXACT(LEFT(B2),LEFT(C2))

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Formula needs to compare *cases* (upper/lower) of FIRST LETTER and update words correc

    @skydivetom,

    You seem to imply that you want the result in column C, but if you want to use a formula it will have to be in a different column, otherwise it will overwrite the value that is in column C to start with. So, you can use this formula in H2 (for example):

    =LEFT(B2)&RIGHT(C2,LEN(C2)-1)

    Copy down to the bottom of your data. You could then use copy | paste special | values to copy the results of the formula over to column C.

    Hope this helps.

    Pete

  5. #5
    Forum Contributor
    Join Date
    08-06-2018
    Location
    Virginia
    MS-Off Ver
    2019
    Posts
    333

    Re: Formula needs to compare *cases* (upper/lower) of FIRST LETTER and update words correc

    BMV, Pete_UK:

    Thank you... your formula(s) work great! I appreciate the help.

+ 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. Replies: 2
    Last Post: 09-19-2017, 03:27 PM
  2. [SOLVED] Cell's insensitive for upper lower letter case
    By Juolupuki in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-13-2017, 07:21 PM
  3. Replies: 7
    Last Post: 04-18-2014, 04:11 PM
  4. [SOLVED] UPPER/LOWER words Formula
    By Dnyan in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-18-2013, 04:27 AM
  5. [SOLVED] Upper and Lower Cases of Text
    By Usif in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-09-2013, 11:32 PM
  6. Replies: 6
    Last Post: 01-18-2013, 12:01 PM
  7. [SOLVED] How do I change Letter case (lower to Upper) in a spreadsheet???
    By mineisjosh in forum Excel General
    Replies: 4
    Last Post: 02-19-2005, 10:06 PM

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