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!

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


On Feb 17, 5:04*pm, LHearn wrote:
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!

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.


"LHearn" wrote:

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!

try the Mid Function

Stew

"LHearn" wrote:

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!

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.)


--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"LHearn" wrote:

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!

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

"LHearn" wrote in message
...
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!