+ Reply to Thread
Results 1 to 7 of 7

VBA to add space before every capial letter EXCLUDING Acroynms

  1. #1
    Registered User
    Join Date
    12-17-2012
    Location
    us
    MS-Off Ver
    excel 2010
    Posts
    8

    VBA to add space before every capial letter EXCLUDING Acroynms

    Hi there,
    Hoping some guru can help me modify a VBA. I have a column that collapsed all the text, removing spaces. However there are capital letters throughout the string that can make the field more readable if I add a space before. I am using the code below, which works nicely, but the issue I am running into is where an acroymn is listed (in all caps) it is adding a space before each letter by design. How can I get it to ignore subsequent caps. For example, if my original string was

    SCUBADivingIsMyFavoritePastime

    the code adjusts to

    S C U B A Diving Is My Favorite Pastime

    where i'd like to have it as

    SCUBA Diving Is My Favorite Pastime

    the acronym may be in the beginning, middle, or end and there may be more than one in the string, any thoughts? Thanks in advance

    Please Login or Register  to view this content.

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: VBA to add space before every capial letter EXCLUDING Acroynms

    Try
    Please Login or Register  to view this content.

  3. #3
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: VBA to add space before every capial letter EXCLUDING Acroynms

    Perhaps:

    Please Login or Register  to view this content.
    Rory

  4. #4
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: VBA to add space before every capial letter EXCLUDING Acroynms

    Hi sryan429,

    Give this a try:

    Please Login or Register  to view this content.
    Last edited by Arkadi; 02-28-2019 at 10:42 AM. Reason: Fixed an issue
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  5. #5
    Registered User
    Join Date
    12-17-2012
    Location
    us
    MS-Off Ver
    excel 2010
    Posts
    8

    Re: VBA to add space before every capial letter EXCLUDING Acroynms

    Hi jindon,
    This worked perfectly! thank you Would you be able to offer a modification as to how to set the range as column E2: thru last value of E? as opposed to highlighting the range prior to running?

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: VBA to add space before every capial letter EXCLUDING Acroynms

    Try change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    12-17-2012
    Location
    us
    MS-Off Ver
    excel 2010
    Posts
    8

    Re: VBA to add space before every capial letter EXCLUDING Acroynms

    thank you!

+ 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] Space after Capital Letter
    By Dineth in forum Excel General
    Replies: 8
    Last Post: 10-10-2023, 04:23 AM
  2. [SOLVED] Excluding text up to the last capital letter from the right
    By Folander in forum Excel General
    Replies: 4
    Last Post: 10-09-2018, 08:23 AM
  3. to use endxlup. row to find the last used row number excluding space due to formula
    By catchnanan in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-18-2016, 05:19 PM
  4. Replies: 25
    Last Post: 04-21-2015, 10:19 AM
  5. Putting a space before a letter, after a number
    By Monkey106 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-01-2010, 06:33 PM
  6. space between evey letter
    By james.halliwell in forum Excel General
    Replies: 1
    Last Post: 04-03-2009, 08:10 AM
  7. excluding data which ends with letter
    By treborharris in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 11-20-2007, 07:20 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1