Excel guru required

[NakedDeadGuy]

Anyone able to help with excel here? I'm not real good with it, thought this would be as agood a place as any to ask for help.
Basic rundown of what I'm trying to do is: I have a list which all appears in one column eg 1 Midnight Oil Diesel & Dust Australia 3 weeks. What I would like to do is seperate the categories without having to do it manually, individual columns for #, artist name, album, country & time. Is there some way I can search the column for "Midnight Oil", and where it finds an instance of that to place it in the column next to it? Hope that makes sense, any help appreciated.

Cheers

 

[Borscht Mat]

yes

you want to use TEXT functions

insert>>function then text on th left hand side

These are essentially


to specifically answer your question

=FIND("midnight oil',A3) with return a number (eg 3) which means that the text you searched for starts at the 3rd character in the string. If you then do:

=LEFT(A3,3) will give you everything before "Midnight Oil"

=MID(A3,4,99( will give you right right most characters including midnight oil, assuming the field is not more than 99 charactrers long...a better way of foing this would be

=RIGHT(A3,4,(LEN(A3)-3)) when LEN(A3) gives the lenth of the field.

It is of course easier if your original field is structured in a common way....eg it always starts with a number and then a space, and then the name of the band, and then another number. You can then use FIND(" ", A3) to tell you where the spaces are and you would not need to hard code "midnight oil" into your formula....which means you would then cut and paste the formula and the job would be done.

When you do find the position of the band or the space of course store this in a cell you can then call, or just nest it in the formula....you would never actually right LEFT (A3,3)..."3" is where "Midnight Oil" starts and you would have this in another cell.....LEFT (A3,FIND("midnight oil',A3)) would work just as well.

Clear as mud I suspect

 

[Mobbs]

Yep Borscht is on the ball, just a heap of text extracting formulas will do it, but as he said, its likely to be difficult if your text is something like :

"MIDNIGHT OIL DIESEL AND DUST"

and you want to separate album from artist, as Excel has no way to know whether the artist is :

"MIDNIGHT" or
"MIDNIGHT OIL" or etc.

unless all your bands have two words you're in a bit of strife!

 

[NakedDeadGuy]

Worked like a chyarm for what I wanted to do, thanks for the help

 

[Borscht Mat]

glad to be of service

$100