+ Reply to Thread
Results 1 to 7 of 7

Trying to add spaces between capitalized words with a formula

  1. #1
    Registered User
    Join Date
    02-07-2012
    Location
    LA
    MS-Off Ver
    Excel For Mac 2011
    Posts
    99

    Trying to add spaces between capitalized words with a formula

    Hi, I'm trying to find a formula to add spaces in between capitalized words

    example :

    cell A1 = MtVernonRoad

    trying to make it say..

    Mt Vernon Road


    any help would be awesome. I'm stumped. thanks

  2. #2
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2010
    Posts
    2,309

    Re: Trying to add spaces between capitalized words with a formula

    so this works for this example, but it is not dynamic for others....unless the whole data set follows this format...

    Please Login or Register  to view this content.
    Ernest

    Please consider adding a * if I helped

    Nothing drives me crazy - I'm always close enough to walk....

  3. #3
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Trying to add spaces between capitalized words with a formula

    A small UDF will work:

    Please Login or Register  to view this content.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  4. #4
    Registered User
    Join Date
    02-07-2012
    Location
    LA
    MS-Off Ver
    Excel For Mac 2011
    Posts
    99

    Re: Trying to add spaces between capitalized words with a formula

    Much thanks OllyXLS , But I'm only familiar with functions that start with = . What is a UDF? I'll look it up on here and try to learn about them. thanks

  5. #5
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Trying to add spaces between capitalized words with a formula

    UDF = User Defined Function.

    You need to paste that code into a module of your workbook:
    - Open the Visual Basic Editor (You're on Excel 2011 for Mac, right? See this link for details of how to do this step)
    - Click Insert > Module
    - Paste my code into the blank module that appears (Module1)
    You may now close the Visual Basic Editor, to return to your workbook.


    You can then use the function in your workbook, as a formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    02-07-2012
    Location
    LA
    MS-Off Ver
    Excel For Mac 2011
    Posts
    99

    Re: Trying to add spaces between capitalized words with a formula

    amazing , thank you OllyXLS . I'll try it

  7. #7
    Registered User
    Join Date
    02-07-2012
    Location
    LA
    MS-Off Ver
    Excel For Mac 2011
    Posts
    99

    Re: Trying to add spaces between capitalized words with a formula

    I couldn't try the UDF because I the section where I needed to paste it gave an error. I did dsome research and it looks like I somehow did not install VBA when I initially installed Excel years back . So, I'll try and remedy that and then try it and report back. Much Thanks

+ 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] formula to copy only capitalized WORDS from a cell
    By emymeeky in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 12-28-2013, 12:56 AM
  2. Extract Capitalized Words From String
    By jaslake in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-12-2010, 07:26 PM
  3. Formula to remove spaces between words
    By jasonmcbride in forum Excel General
    Replies: 2
    Last Post: 03-31-2009, 08:54 PM
  4. [SOLVED] CAPITALIZED ALL WORDS FROM WROKSHEET
    By convert all capital letter from excel in forum Excel General
    Replies: 2
    Last Post: 05-01-2006, 08:45 AM
  5. [SOLVED] convert capitalized text to small text (with Capitalized names an
    By trailboss2 in forum Excel General
    Replies: 2
    Last Post: 10-05-2005, 06:05 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