[thelist] Splitting the contents of an Excel cell

[Chris Spruck]

At 06:55 PM 8/19/02 +0100, fstorr wrote:
>Hi all
>
>Don't know if this is possible.
>
>I've been given a large (the equivalent of 50 pages of A4) Excel
>document that I need to reformat for a database.
>
>The data is currently like this (pseudo stuff)
>
>Cell
>Artist Name/Record Title
>/Cell
>
>
>And I'd like it to be
>
>Cell
>Artist Name
>/Cell
>Cell
>Record Title
>/Cell
>
>Is there any way possible to separate the single line of text into 2
>different cells and remove the slash?

Frances,

Take a look at the string functions in Excel too. Here's a forumla using string functions that will do the trick for you, assuming you have Artist Name/Record Title in cell A1. You can change the cell references by hand if needed, then drag-copy them down the rest of your data column

=LEFT(A1, (FIND("/", A1) -1)) yields Artist and
=RIGHT(A1, FIND("/", A1)) yields Record Title

These read as "take the left (or right) x number of characters from A1" where the FIND clause returns the starting position of the string "/", then in the case of the Left formula, you subtract one from that position number to exclude the / itself.

HTH!

Chris