Results 1 to 18 of 18

Excel 2008 : Finding Consecutive Identical Upper Case Letters

Threaded View

  1. #1
    Registered User
    Join Date
    05-16-2011
    Location
    Tucson, AZ
    MS-Off Ver
    Excel 2008 For Mac
    Posts
    7

    Finding Consecutive Identical Upper Case Letters

    I downloaded a 1500 line file containing song titles in which all the spaces between words have been omitted. Here is a sample:
    NowIsTheTimeForAllGoodMen(ToComeToTheAidOfTheirCountrymen).mp3

    Some lines contain 25 words and one is 132 chars long.

    In the General forum, I found this formula for determining the location of an upper case letter:
    ={SMALL(FIND(0,SUBSTITUTE(A5,CHAR(ROW(INDIRECT("65:90"))),0)&0),2)}

    Using it, and and LEFT and MID functions repeatedly, I have been able to locate the next upper case letter, add a space before it, isolate each word in a column, and concatenate all the individual words at the end to restore the complete title with spaces between words.

    In general, this works very well, but there are two problems, one I can't easily work around:

    1) Parentheses: Wherever there is a left parenthesis, the space should be added before, not after the parenthesis (which is exactly what I told it to do). I can do a simple replace at the end to fix it, but perhaps there is a more elegant method.
    2) Consecutive Identical Upper Case Letters: If there are two or more uppercase letters in a row which are identical, only the first one is picked up by the SMALL function. This is NOT easy to work around and is where I really need help.

    Here is the end result using the example provided above:
    Now Is TheTime For All Good Men( To Come ToThe Aid Of Their Countrymen).mp3

    This is my first post, sorry if I violated any rules.
    Last edited by mikeaq; 05-19-2011 at 06:26 PM. Reason: SOLVED

Thread Information

Users Browsing this Thread

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

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