Formula to rearrange contents of a cell

[Whougonacall]

Example: Cell A2 displays the following contents:
02/27/2007
is there a formula I can use to rearrange the cell contents to change to
20070227

[Carim]

Hi,

If cell A2 is a clean date format, you just need to Format Cell Custom
yyyymmdd ...

[starguy]

Example: Cell A2 displays the following contents:
02/27/2007
is there a formula I can use to rearrange the cell contents to change to
20070227

you need to do some work
select the entire column which contains such type of data.
go to menu Data > Text to Columns... and use / as delimiter.
it will split you data in three columns (say col A, B and C).
now in col D put following function.
=C1&B1&A1 and copy it down.

(you can copy data to a blank sheet to try this procedure)

[oldchippy]

Example: Cell A2 displays the following contents:
02/27/2007
is there a formula I can use to rearrange the cell contents to change to
20070227

Try this,

=DATE(RIGHT(A2,4),LEFT(A2,2),MID(A2,4,2)) custom format cell to yyyymmdd

[starguy]

Try this,

=DATE(RIGHT(A2,4),LEFT(A2,2),MID(A2,4,2)) custom format cell to yyyymmdd

Hi Oldchippy

Carim's tip seems better than using formula, because you even need to change format of the cell as Carim mentioned.

[oldchippy]

Hi Oldchippy

Carim's tip seems better than using formula, because you even need to change format of the cell as Carim mentioned.

Hi starguy,

Agreed Carim's works fine if the date is in a number format, but if it is text that doesn't work.

[Whougonacall]

Thanks for your help guys I will give this a try and get back to you with which way worked best for me

[Whougonacall]

Try this,

=DATE(RIGHT(A2,4),LEFT(A2,2),MID(A2,4,2)) custom format cell to yyyymmdd

Thank you Old Chippy!

But this turned cell a2's contents of 02/27/2007 into 27/02/2007 so it just turned MM/DD/YYYY into DD/MM/YYYY

How can I urn it into YYYYMMDD (please note I am not using fwd slash here as I would rather this without)

So far Star Guy's suggesion has work, however i'd be interested in a formula that can do this too.

Thanks for our help

[oldchippy]

Thank you Old Chippy!

But this turned cell a2's contents of 02/27/2007 into 27/02/2007 so it just turned MM/DD/YYYY into DD/MM/YYYY

How can I urn it into YYYYMMDD (please note I am not using fwd slash here as I would rather this without)

So far Star Guy's suggesion has work, however i'd be interested in a formula that can do this too.

Thanks for our help

Hi Whougonacall,

Use the formula I have suggested, then right click on the cell with 27/02/2007, Format cells > Category, choose Custom, you will see in the "Type" box dd/mm/yy, click in the box and change to yyyymmdd click OK. Your date now is 20070227 exactly as you wanted

Hope this helps

[Whougonacall]

Thanks for the info this is great allworked fine

[oldchippy]

Glad to hear we have a result - thanks for the feedback