+ Reply to Thread
Results 1 to 9 of 9

Adding in a comma before every capital letter?

  1. 05-02-2018, 07:32 AM #1
    danc1994
    danc1994 is offline
    Registered User
    Join Date
    05-02-2018
    Location
    london
    MS-Off Ver
    2013
    Posts
    6

    Adding in a comma before every capital letter?

    Hi all,

    Apologies if this sin't formatted correctly as it's my first post...

    basically I have a 1539 cells each with a list of various subjects. A typical example is; Children's poetry,Children's songs,ChildrenSocial classesBlasphemyRidicule

    Is there a macro I can use to automatically insert a comma before each capital so the cell would be; Children's poetry,Children's songs,Children,Social classes,Blasphemy,Ridicule

    Or any quicker way than me going through each cell and manually adding a comma.

    Thanks!
    Reply With QuoteRegister To Reply
  2. 05-02-2018, 07:54 AM #2
    PaulM100
    PaulM100 is offline
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: Adding in a comma before every capital letter?

    Here you can find some solutions:

    https://www.excelforum.com/excel-for...r-in-cell.html
    Reply With QuoteRegister To Reply
  3. 05-02-2018, 11:34 AM #3
    danc1994
    danc1994 is offline
    Registered User
    Join Date
    05-02-2018
    Location
    london
    MS-Off Ver
    2013
    Posts
    6

    Re: Adding in a comma before every capital letter?

    Thank you for reply - although i originally tried this solution before posting and I couldn't get it to work - I kept receiving a #NAME? error
    Reply With QuoteRegister To Reply
  4. 05-02-2018, 12:03 PM #4
    dominicb
    dominicb is offline
    Forum Expert dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Re: Adding in a comma before every capital letter?

    Good afternoon danc1994

    Try this - it's a UDF rather than a macro but it should work OK for your needs.

    Please Login or Register  to view this content.
    Regards

    DominicB
    Reply With QuoteRegister To Reply
  5. 05-02-2018, 12:18 PM #5
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Adding in a comma before every capital letter?

    Just for fun with a long formula
    PHP Code: 
    =MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"A",",A"),"B",",B"),"C",",C"),"D",",D"),"E",",E"),"F",",F"),"G",",G"),"H",",H"),"I",",I"),"J",",J"),"K",",K"),"L",",L"),"M",",M"),"N",",N"),"O",",O"),"P",",P"),"Q",",Q"),"R",",R"),"S",",S"),"T",",T"),"U",",U"),"V",",V"),"W",",W"),"X",",X"),"Y",",Y"),"Z",",Z"),2,10000) 
    v A B
    1 Children's poetryChildren's songsChildrenSocial classesBlasphemyRidicule Children's poetry,Children's songs,Children,Social classes,Blasphemy,Ridicule
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne
    Reply With QuoteRegister To Reply
  6. 05-02-2018, 12:27 PM #6
    Pete_UK
    Pete_UK is online now
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,733

    Re: Adding in a comma before every capital letter?

    @AlKey,

    the original example already had two commas in, so perhaps you should have another SUBSTITUTE to change a double comma to a single.

    Hope this helps.

    Pete
    Reply With QuoteRegister To Reply
  7. 05-02-2018, 12:30 PM #7
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Adding in a comma before every capital letter?

    Yea, I see
    Here a changed formula
    PHP Code: 
    =MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,",",""),"A",",A"),"B",",B"),"C",",C"),"D",",D"),"E",",E"),"F",",F"),"G",",G"),"H",",H"),"I",",I"),"J",",J"),"K",",K"),"L",",L"),"M",",M"),"N",",N"),"O",",O"),"P",",P"),"Q",",Q"),"R",",R"),"S",",S"),"T",",T"),"U",",U"),"V",",V"),"W",",W"),"X",",X"),"Y",",Y"),"Z",",Z"),2,10000) 
    Reply With QuoteRegister To Reply
  8. 05-02-2018, 06:11 PM #8
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Adding in a comma before every capital letter?

    Picking up Dominic's #4 I think it needs a

    NText = Replace(NText, ",,", ",")
    immediately after the 'Next n'

    And perhaps you might need to Dim the AsciVal variable as a Long
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
    Reply With QuoteRegister To Reply
  9. 05-10-2018, 07:10 AM #9
    danc1994
    danc1994 is offline
    Registered User
    Join Date
    05-02-2018
    Location
    london
    MS-Off Ver
    2013
    Posts
    6

    Re: Adding in a comma before every capital letter?

    Thanks very much Dominic, this worked great
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Automatic capital letter for each sentence First letter - how
    By santbiju1 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-10-2015, 06:21 AM
  2. Insert Comma before first Capital Letter in cell
    By Kevy Kev in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 05-11-2014, 10:25 AM
  3. lower case letter to capital letter
    By kuzna26 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-10-2014, 03:49 AM
  4. [SOLVED] first letter small case & all letter capital case any coding / any trick / any formula
    By sonu_kumar444 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-22-2012, 05:22 AM
  5. capital letter
    By holybull in forum Excel General
    Replies: 7
    Last Post: 09-27-2009, 04:30 PM
  6. New Validation option to format 1st letter as Capital letter
    By Jeff in forum Excel General
    Replies: 5
    Last Post: 07-13-2006, 12:15 AM
  7. Default Capital letter for 1st letter of a word
    By Jeff in forum Excel General
    Replies: 6
    Last Post: 07-10-2006, 03:39 AM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1