+ Reply to Thread
Results 1 to 5 of 5

Modified PROPER formula

  1. #1
    Registered User
    Join Date
    03-05-2021
    Location
    Poland
    MS-Off Ver
    365
    Posts
    31

    Modified PROPER formula

    Hi Guys,

    I'm looking for a solution to automatically change names' first letters to capital letters, but with some exceptions. I know that PROPER formula would do that, but let's say I have many names with de, da etc., like Marco da Costa, is there any chance then to write the formula in the way so in case there are 3 name components, they will keep the 2nd in lower cases, but when it's like Marco Costa, then it will keep it both with capital letters? I hope it makes sense. Thank you!
    Last edited by kf1511; 06-18-2021 at 07:37 AM.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,344

    Re: Modified PROPER formula

    What if "John James Brown"? You may need to define exceptions like "de", "da".

    See yellow banner of how to post a file. Please give a representative sample: if required can VBA be used?

  3. #3
    Registered User
    Join Date
    03-05-2021
    Location
    Poland
    MS-Off Ver
    365
    Posts
    31

    Re: Modified PROPER formula

    Hi,

    Thanks for responding! No, there will be no cases of middle names. Plus I will have hundreds of rows with different de, da, von etc so I'd like to avoid catching them first and then excluding them by putting each one of them in the formula. I'd like to avoid VBA, but if there's absolutely no way to avoid it then it's ok. I attached the example - so I'd like to have them like:

    Marco da Costa
    John Smith
    Richard von Graff
    Anne Black
    Maria de Suarez
    Attached Files Attached Files
    Last edited by kf1511; 06-15-2021 at 10:50 AM.

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,344

    Re: Modified PROPER formula

    Try

    =IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=2,PROPER(LEFT(A1,FIND(" ",A1)-1))&" " &MID(A1, SEARCH(" ", A1) + 1, SEARCH(" ", A1, SEARCH(" ", A1)+1) - SEARCH(" ", A1)-1)& " " &PROPER(RIGHT(A1,LEN(A1)-SEARCH("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))),PROPER(A1))
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-05-2021
    Location
    Poland
    MS-Off Ver
    365
    Posts
    31

    Re: Modified PROPER formula

    Amazing, thank you John!

+ 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] VBA to have Modified by and Modified on column for all rows
    By gmalpani in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-28-2020, 03:21 PM
  2. [SOLVED] Can this Effective Ranking Formula be Modified?
    By ephkay in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-11-2018, 11:17 AM
  3. modified MID or REPLACE formula
    By drewz5 in forum Excel General
    Replies: 6
    Last Post: 07-07-2011, 03:54 PM
  4. Modified Average Formula
    By rhudgins in forum Excel General
    Replies: 8
    Last Post: 12-10-2010, 03:32 PM
  5. Modified 'Max' formula?
    By greeng66 in forum Excel General
    Replies: 2
    Last Post: 05-17-2010, 11:46 AM
  6. Modified VBA Formula Help!
    By vane0326 in forum Excel General
    Replies: 0
    Last Post: 03-03-2005, 06:29 PM
  7. Proper Syntax for Date Modified?
    By Marty in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-29-2005, 01: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