+ Reply to Thread
Results 1 to 13 of 13

Text functions to add X character in between sentences

  1. #1
    Registered User
    Join Date
    04-05-2014
    Location
    São Paulo, Brasil
    MS-Off Ver
    Excel 2007
    Posts
    12

    Text functions to add X character in between sentences

    Hello everyone,

    I have thousands of track titles into my music library that requires some organization.
    In this case below, I need to add a "\" character to split the track number from its title.
    So, the problem remains at the lack of pattern in the text and the only reference it the beginning of the song title (but it also can begin with a text or a number).Text.jpg


    From: 01 - It's A Long Way To The Top.mp3 >>>>>>>>>> To: 01\It's A Long Way To The Top.mp3
    From: 02-Crazy.mp3 >>>>>>>>>> To: 02\Crazy.mp3
    From: 3-01 Need Your Love.m4a >>>>>>>>>> To: 3-01\Need Your Love.m4a
    From: 10-13 Angels.mp3 >>>>>>>>>> To: 10-13\Angels.mp3
    From: 01 10_15 Saturday Night.m4a >>>>>>>>>> To: 01\10:15 Saturday Night.m4a
    From: 3 - Guitar Mafia.mp3 >>>>>>>>>> To: 3\Guitar Mafia.mp3
    From: 1-01 Losing all.m4a >>>>>>>>>> To: 1-01\Losing All My Friends.m4a

  2. #2
    Valued Forum Contributor
    Join Date
    12-02-2012
    Location
    Melbourne, VIC
    MS-Off Ver
    Excel 2016
    Posts
    750

    Re: Text functions to add X character in between sentences

    Instead of looking for a one-size-fits-all, can you not identify & fix each of these case separately.
    e.g. Case 2 looks easiest
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    This way you'd be able to solve one case at a time; and eventually solve all of it.

  3. #3
    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: Text functions to add X character in between sentences

    Well, this is one ugly formula

    =IF(ISERROR(SEARCH("\",SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",REPT(" ",10),1)," ","\",1),"_",":",1)),"\ ","\"),"\- ","\"))),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",REPT(" ",10),1)," ","\",1),"_",":",1)),"\ ","\"),"\- ","\"),"-","\"),SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",REPT(" ",10),1)," ","\",1),"_",":",1)),"\ ","\"),"\- ","\"))

    A
    B
    1
    01 - It's A Long Way To The Top.mp3 01\It's A Long Way To The Top.mp3
    2
    02-Crazy.mp3 02\Crazy.mp3
    3
    3-01 Need Your Love.m4a 3-01\Need Your Love.m4a
    4
    10-13 Angels.mp3 10-13\Angels.mp3
    5
    01 10_15 Saturday Night.m4a 01\10:15 Saturday Night.m4a
    6
    3 - Guitar Mafia.mp3 3\Guitar Mafia.mp3
    7
    1-01 Losing all.m4a 1-01\Losing all.m4a
    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

  4. #4
    Valued Forum Contributor
    Join Date
    12-02-2012
    Location
    Melbourne, VIC
    MS-Off Ver
    Excel 2016
    Posts
    750

    Re: Text functions to add X character in between sentences

    .... or you can do it the "expert" way. I, for one, don't think its ugly - tis just well customized!!

  5. #5
    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: Text functions to add X character in between sentences

    @jewelsharma, Thank you for the complement I hope someone will come up the a better solution for this brain-twister.

  6. #6
    Registered User
    Join Date
    04-05-2014
    Location
    São Paulo, Brasil
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Text functions to add X character in between sentences

    Curiously, it did not run right for similar text:

    11 No Place Like Home.m4a >>>> result: 'remained the same'
    01 Bug (live).mp3 >>>> result: 01\Bug (live).mp3

  7. #7
    Registered User
    Join Date
    04-05-2014
    Location
    São Paulo, Brasil
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Text functions to add X character in between sentences

    Interesting... it did not work for all the cells...

    Check this out...

    04-Blackout.mp3 04\Blackout.mp3
    05-Get Away.mp3 05-Get\Away.mp3

  8. #8
    Valued Forum Contributor
    Join Date
    12-02-2012
    Location
    Melbourne, VIC
    MS-Off Ver
    Excel 2016
    Posts
    750

    Re: Text functions to add X character in between sentences

    Well, it shouldn't have worked on either, it was supposed to check if the 3rd character is a 'hyphen' and if so, replace it with 'backslash'; else leave the string unchanged. But anyways, the broader idea was to handle 1-type of case each time; adjust the formula for the 2nd case and so on - so eventually all cases would have been modified as desired.

    But then, we have AlKey's solution - which just solves everything in 1 go. This is exactly what makes this forum such a great place. We have ideas, we have solutions, and every once in a while, we get super-solutions that teach us all something new.

  9. #9
    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: Text functions to add X character in between sentences

    I added your examples and everything seems fine. Here is an attached spreadsheet.
    Attached Files Attached Files

  10. #10
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Text functions to add X character in between sentences

    Hope I am more handsome than AlKey

    Please Login or Register  to view this content.
    Last edited by bebo021999; 04-07-2014 at 01:31 AM.
    Quang PT

  11. #11
    Registered User
    Join Date
    04-05-2014
    Location
    São Paulo, Brasil
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Text functions to add X character in between sentences

    Hi buddy, thanks for helping. But it did not work. File is attached.
    Attached Files Attached Files

  12. #12
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Text functions to add X character in between sentences

    Quote Originally Posted by dvpe View Post
    Hi buddy, thanks for helping. But it did not work. File is attached.
    Sorry not to notice that is an array formula.

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

  13. #13
    Registered User
    Join Date
    04-05-2014
    Location
    São Paulo, Brasil
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Text functions to add X character in between sentences

    Got it!
    It is alright now! Perfect!

    Thank you dude!

+ 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. Replies: 4
    Last Post: 04-06-2014, 01:21 PM
  2. Macro to find character and delete all text in cell after the character
    By SpencerRichman in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-01-2012, 06:08 PM
  3. Upper only first character in first word of sentences
    By cuongvt in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-31-2007, 06:03 AM
  4. importing undelimited text file data, character-by-character
    By The Mos$ in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-26-2005, 07:10 AM
  5. Replies: 1
    Last Post: 04-17-2005, 08:10 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