Split and Parse Data into Separate Cells

[Danno0772]

Hi

I have a range of cells that I have copied and pasted from a directory file list. I'm trying to split and parse the data so that all I get is a column containing just the file name without the file type suffix, date, time, spaces and file size.

eg:

20/01/2017  06:29           114,881 Tonic Trouble (USA) (Rev A).png
20/01/2017  06:29            72,482 Tony Hawk's Pro Skater 2 (USA).png
20/01/2017  06:29            57,126 Tom Clancy's Rainbow Six (USA).png
20/01/2017  06:29            87,216 Superman (USA) (En,Fr,Es).png
20/01/2017  06:29            30,068 Supercross 2000 (USA).png
20/01/2017  06:29            67,729 Super Smash Bros. (USA).png
08/01/2018  22:28            52,126 Super Mario Odyssey 64.png
20/01/2017  06:29            91,390 Super Mario 64 (USA).png
20/01/2017  06:29           111,690 Super Bowling (USA).png

Which is all in column A

I want to split this so I just get the file name:

Tony Hawk's Pro Skater (USA)
Tom Clancy's Rainbow Six (USA)
.
.
.
etc

I've tried using text to columns, but even using custom delimiters I cant get it to parse properly.

Then there's the problem of removing the suffix. Some of the filenames have commas and some have fullstop/periods in the title, such as Mr.Somebody.png for example, so again delimiters cannot differentiate between the two.

Any advice greatfully received.

[Sam Capricci]

if they are all .png you can use a simple substitute for that, something like =SUBSTITUTE(A2,".png","") and it will return all but that. If you have other file suffixes you can add additional substitutes for those. I am not sure what the date and time sections have to do with it since I cannot see your data, just the snapshot.

[Sam Capricci]

after rereading, if you have all of that in a given cell (date, time, some count, etc.) it appears that every place where there is a title you want is at the same point so maybe this formula will work better... =SUBSTITUTE(MID(A2,37,99),".png","")

[Danno0772]

Perfect, thanks

[Rick Rothstein]

Here is another formula that should work...

=MID(LEFT(A2,LEN(A2)-4),37,99)