de-concatenate?

[LHearn]

I have text that I would like to de-concatenate. The "left" or "right"
functions will not work, because the text is of varying length (unless there
is another way to utilize these functions that I am not aware of.) Basically,
someone else formatted a spreadsheet with date ranges in one cell (it looks
like 12/1/08-12/31/08). I want each date in its own column, so I would
basically like to de-concatenate it. The only way I have done this before is
using the "left" or "right" functions, but as I said earlier, the date ranges
are of varying length, since some months and days are single digits and some
are double digits. Does anyone know a formula I could try? I want to take
everything before the dash and put it in one cell and everything after the
dash and put it in the other, so it seems like this should be fairly simple,
but I just can't figure it out. There's 3000 cells that need to be split, so
if there is an easy way out other than cutting and pasting, I'd like to take
advantage of it! thanks!

 

[Pete_UK]

Assuming that date range is in A1, you can use this to get the first
date:

=--LEFT(A1,FIND("-",A1)-1)

and this to get the second date:

=--RIGHT(A1,LEN(A1)-FIND("-",A1)-1)

LEFT and RIGHT return text values, so the -- just convert these into
proper dates. Format the cells as dates in the style you wish, then
copy down as required.

Hope this helps.

Pete

 

[Sheeloo]

If all pairs are separated by " - " then you can combine FIND with LEFT,
RIGHT, and LEN to get what you want...

=LEFT(A1,FIND("-",A1)-2)
for first date and
=RIGHT(A1,LEN(A1)-FIND("-",A1)-1)
for second date.

 

[stew]

try the Mid Function

Stew

 

[Shane Devenshire]

Hi,

You might also consider the Data, Text to Columns command with either fixed
width or delimited with "-" as the delimiter. With your sample the delimiter
idea worked great for me:

1. Select all the cells and choose the command Data, Text to Columns
2. Pick Delimited and click Next
3. Enter - in the Other box and click Finish (or Next if you want to change
the destination cell.)

 

[David Biddulph]

Data/ Text to Columns, and specify the hyphen as delimiter.