Excel Question - TExt to Column delemma

[jamieb1970]

Hi,
First post. I'm using Excel 2007 and have a question. I have it mostly figured out but stuck on one thing.

I collect a lot of audio concerts and have them on multiple disk (about 20-25 per disk.)
I am trying to make an excel list of the shows so I can see which I have so I don't wind up with duplicates.

I know I can go into CMD and type DIR /B > test.txt or something similar. then open that file in excel. I don't have
an issue with that. I can get a nice list of files in COLUMN A.

So, I highlight COLUMN A and hit TEXT to COLUMN and this is where the trouble starts to brew.

COLUMN A has a list similar to this

Pink Floyd - 1971-12-22 San Francisco
Pink Floyd - 1980-10-21 New York
Rolling Stones - 2005-10-15 Philadelphia
Styx - 1982-09-17 Boston

ect....

What I want to do is have the band name in one column. The date in the next and finally the city in the third.
I find I can use a SEPERATOR of - but then it also seperates the date into 3 columns and doesn't separate the city.

Any idea how to do this besides hand typing them?
Any help is appreciated as I have over 1,000 shows and hand separating them all is anti-what I want to do LOL.

[etaf]

will you always have 4 dashes before the city

=LEFT(D14, FIND(CHAR(7),SUBSTITUTE(D14,"-",CHAR(7),4)))[/B]

so the first "-" does it always have a space before if so

=LEFT(D14, FIND("-",D14,FIND("-",D14))-1)
will extract the name

then we need to extract the date
so again is the first - always followed by a -
and what does the 1-9 dates look like

2010-1-1 - is that January 1st
2010-01-01

=LEFT(A1, FIND(CHAR(7),SUBSTITUTE(A1,"-",CHAR(7),4))+2)
will move 2 characters to the right of the -
and we could use the Mid function to extract that info

=Mid(A1, FIND(CHAR(7),SUBSTITUTE(D14,"-",CHAR(7),4))+3), Len(A1))
will extract the place

BUT I need to know the format is OK

can you post a sample file with the data in column A

[jamieb1970]

posted file below.

[jamieb1970]

had to use CHROME to upload

[etaf]

so that worked - apart from 1 row
Iron Maiden 2013-06-05 Paris

which does not have the - between the name and date

see attached

once done

I would then copy the range B1 to D??
copy
paste special
Value
so then the formulas are replaced and you have text to do what you like with
and can delete column A with no effect

[etaf]

so in cell
B1
=LEFT(A1, FIND(CHAR(7),SUBSTITUTE(A1,"-",CHAR(7),1))-2)

C1
=MID(A1, FIND(CHAR(7),SUBSTITUTE(A1,"-",CHAR(7),1))+2,10)
but wont work if the dates are
2013/1/1
in which case I would need to add some additional code to capture that

D1
=MID(A1, FIND(CHAR(7),SUBSTITUTE(A1,"-",CHAR(7),3))+3,256)

[jamieb1970]

Cool. Thank you very much.

[newdoverman]

The enclosed should do what you want.