+ Reply to Thread
Results 1 to 11 of 11

Split File Name Into 3 Parts

  1. 01-16-2017, 03:44 AM #1
    bobbyrabbit
    bobbyrabbit is offline
    Registered User
    Join Date
    01-16-2017
    Location
    London
    MS-Off Ver
    Mac - 16.64 (22081401)
    Posts
    10

    Split File Name Into 3 Parts

    Filename format is:
    SKU Artist - Songname.mp3
    eg. AA1000 THE BEATLES - I FEEL FINE.mp3

    I need to split the SKU Artist and songname (without .mp3) and put into their own cells.
    I have accomplished the SKU section by using:
    =LEFT(A2,6)

    I have the songname but having trouble removing the .mp3
    =MID(A2,FIND("-",A2)+1,99)

    Head spinning too much seperate THE BEATLES

    Help appreciated. Thanks
    Reply With QuoteRegister To Reply
  2. 01-16-2017, 04:18 AM #2
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,420

    Re: Split File Name Into 3 Parts

    You could use SUBSTITUTE to replace .mp3 with nothing ("")
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 01-16-2017, 04:19 AM #3
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2504 (Windows 11 Home 24H2 64-bit)
    Posts
    90,897

    Re: Split File Name Into 3 Parts

    Try:

    =MID(A1,FIND(" ",A1)+1,FIND("-",A1)-2-FIND(" ",A1)+1)

    and

    =MID(A1,FIND("-",A1)+2,FIND(".",A1)-2-FIND("-",A1))

    Substitute A1 with A2 if needs be.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  4. 01-16-2017, 04:34 AM #4
    bobbyrabbit
    bobbyrabbit is offline
    Registered User
    Join Date
    01-16-2017
    Location
    London
    MS-Off Ver
    Mac - 16.64 (22081401)
    Posts
    10

    Re: Split File Name Into 3 Parts

    Thanks Ali...perfect
    Reply With QuoteRegister To Reply
  5. 01-16-2017, 06:13 AM #5
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2504 (Windows 11 Home 24H2 64-bit)
    Posts
    90,897

    Re: Split File Name Into 3 Parts

    Glad to have helped!
    Reply With QuoteRegister To Reply
  6. 01-16-2017, 06:31 AM #6
    bobbyrabbit
    bobbyrabbit is offline
    Registered User
    Join Date
    01-16-2017
    Location
    London
    MS-Off Ver
    Mac - 16.64 (22081401)
    Posts
    10

    Re: Split File Name Into 3 Parts

    Just realised I have quite a few hyphenated Artist names. How do I exclude that hyphen
    Reply With QuoteRegister To Reply
  7. 01-16-2017, 07:17 AM #7
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2504 (Windows 11 Home 24H2 64-bit)
    Posts
    90,897

    Re: Split File Name Into 3 Parts

    This is why you should always provide a selection of representative data. Sorry - I am away from my PC at the moment - maybe someone else can help.
    Reply With QuoteRegister To Reply
  8. 01-16-2017, 11:04 AM #8
    Raphaelp
    Raphaelp is offline
    Forum Contributor
    Join Date
    01-05-2017
    Location
    New York
    MS-Off Ver
    Office 2016
    Posts
    206

    Re: Split File Name Into 3 Parts

    Does Your excel have "flash fill"? (It should be on the Data tab, in the 'Data tools' section.) Assuming your file names are in A1, go to B1, Manually type in the first artist name, press enter. Then Click on "Flash fill" and see what happens.
    Reply With QuoteRegister To Reply
  9. 01-16-2017, 10:24 PM #9
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 V 2504
    Posts
    13,740

    Re: Split File Name Into 3 Parts

    Are any of the songs hyphenated as well?
    Dave
    Reply With QuoteRegister To Reply
  10. 01-17-2017, 04:54 AM #10
    bobbyrabbit
    bobbyrabbit is offline
    Registered User
    Join Date
    01-16-2017
    Location
    London
    MS-Off Ver
    Mac - 16.64 (22081401)
    Posts
    10

    Re: Split File Name Into 3 Parts

    Quote Originally Posted by FlameRetired View Post
    Are any of the songs hyphenated as well?
    Now you mention it there are one or two

    Quote Originally Posted by Raphaelp View Post
    Does Your excel have "flash fill"? (It should be on the Data tab, in the 'Data tools' section.) Assuming your file names are in A1, go to B1, Manually type in the first artist name, press enter. Then Click on "Flash fill" and see what happens.
    Thanks for pointing out Flash Fill, that could have saved me a huge amount of time.
    Reply With QuoteRegister To Reply
  11. 01-17-2017, 11:41 AM #11
    Raphaelp
    Raphaelp is offline
    Forum Contributor
    Join Date
    01-05-2017
    Location
    New York
    MS-Off Ver
    Office 2016
    Posts
    206

    Re: Split File Name Into 3 Parts

    Sure thing!
    Last edited by Raphaelp; 01-17-2017 at 11:43 AM.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. split the sentence in to four parts
    By saravanan1981 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-22-2015, 04:56 AM
  2. formatting of split parts
    By hutia in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 06-04-2015, 08:55 AM
  3. Split long text in two parts
    By Marvin85 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-23-2014, 03:12 PM
  4. [SOLVED] Split a string into parts
    By LeahS in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-01-2013, 09:33 AM
  5. [SOLVED] Dim String, and divide/split into parts
    By Hallet in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-19-2012, 12:37 PM
  6. Split a number in a cell into parts
    By okanem in forum Excel General
    Replies: 4
    Last Post: 08-21-2006, 11:05 AM
  7. Split Number into Four Separate Parts
    By Gos-C in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-16-2005, 02:08 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1