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.
Bookmarks