Board index » Excel » Range split

Excel76
Hi



Somebody help me



how can I copy dd, mm and yy from a text "dd/mm/yy" to 3 separat

cells.



thanks and regards

HM



--

hm

-----------------------------------------------------------------------

hme's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1593

View this thread: http://www.excelforum.com/showthread.php?threadid=32002


-

Without code



=DAY(A1)

=MONTH(A1)

=YEAR(A1)



--



HTH



RP

(remove nothere from the email address if mailing direct)





"hme" <hme.1g7wpz@excelforum-nospam.com>wrote in message



-

If your date is in cell A1:

=DAY(A1)

=MONTH(A1)

=YEAR(A1)



or by vba:



Sub test()

Dim str As String, dtm As Date



'example 1

str = "13/01/04"



Debug.Print Left(str, 2)

Debug.Print Mid(str, 4, 2)

Debug.Print Right(str, 2)



'example 2

dtm = str

Debug.Print Day(dtm)

Debug.Print Month(dtm)

Debug.Print Year(dtm)

End Sub





--

Rob van Gelder - http://www.vangelder.co.nz/excel





"hme" <hme.1g7wpz@excelforum-nospam.com>wrote in message



-

You could type the following directly into the target cells:



=day(A1)

=month(A1)

=year(A1)



Note: A1 contains a date .



This is sensitive to the short date setting in regional settings. Or, use

the following with VBA.



Format("yourdate","dd")

Format("yourdate","mm")

Format("yourdate","yyyy")





"hme" wrote:

cell C6 has string 11/12/04



any cell type

=LEFT(C6,2)

gives11

another cell type

=MID(C6,4,2)

gives 12

yet another cell type

=RIGHT(C6,2)

gives 04



is this what you want



==========

hme <hme.1g7wpz@excelforum-nospam.com>wrote in message



-