i have a list of dates 'DD,MM,YYYY' and i need to split the cells so that in
a new collum it has 'DD' 'MM' 'YYYY' i can understand how to put the day and
year into a new collum =left(A1,2) & =right(A1,4) but i cant work out how to
seperate the middle

should i delete the commas?

thanks in advance
Rose

If your data is entered as a true date, why not just change the formatting of
the cell? (use custom format)

If not, use
=SUBSTITUTE(A1,","," ")
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"RowzeRiot" wrote:

i have a list of dates 'DD,MM,YYYY' and i need to split the cells so that in
a new collum it has 'DD' 'MM' 'YYYY' i can understand how to put the day and
year into a new collum =left(A1,2) & =right(A1,4) but i cant work out how to
seperate the middle

should i delete the commas?

thanks in advance
Rose

Hi

You can use a mid formula so if your cell shows as 01,03,2009:

Mid(A1,4,2)

4 is the character your want to start with from the left i.e. 0 and the 2 is
number of characters you want i.e 0 & 3.

--
James.


"RowzeRiot" wrote:

i have a list of dates 'DD,MM,YYYY' and i need to split the cells so that in
a new collum it has 'DD' 'MM' 'YYYY' i can understand how to put the day and
year into a new collum =left(A1,2) & =right(A1,4) but i cant work out how to
seperate the middle

should i delete the commas?

thanks in advance
Rose

Highlight your column go to Data - Text to Column - check the Deliminated
with - check the "comma" and finish. It will split that cell into three
columns.
Hope this helps.
Monika

"RowzeRiot" wrote:

i have a list of dates 'DD,MM,YYYY' and i need to split the cells so that in
a new collum it has 'DD' 'MM' 'YYYY' i can understand how to put the day and
year into a new collum =left(A1,2) & =right(A1,4) but i cant work out how to
seperate the middle

should i delete the commas?

thanks in advance
Rose

Thankyou veyr much :)

"James" wrote:

Hi

You can use a mid formula so if your cell shows as 01,03,2009:

Mid(A1,4,2)

4 is the character your want to start with from the left i.e. 0 and the 2 is
number of characters you want i.e 0 & 3.

--
James.


"RowzeRiot" wrote:

i have a list of dates 'DD,MM,YYYY' and i need to split the cells so that in
a new collum it has 'DD' 'MM' 'YYYY' i can understand how to put the day and
year into a new collum =left(A1,2) & =right(A1,4) but i cant work out how to
seperate the middle

should i delete the commas?

thanks in advance
Rose