Problem with a date and concatenation

[jerry.ranch]

I got some data out of one of our in house databases and it comes out
as 01/022206, for example..a missing second "/" (we need to FIX that
damn databse too)

I have thousands of these rows, and I need to do some analyses by
date, so I need a correct date.

So I was going to use text to columns> fixed width and separate the
01/02 from the 2006, then I would concatenate with
(replacing the actual contents with the cell name of course)

="01/02"&"/"&"2006"


But when I do the text to columns, I get the 2006 but I I don't get
01/02.. I get a serial number(even when formatting as text) and then
when I contencate I get garbage


Need help
Thanks
Jerry

 

[Martin]

I got some data out of one of our in house databases and it comes out
as 01/022206, for example..a missing second "/" (we need to FIX that
damn databse too)

I have thousands of these rows, and I need to do some analyses by
date, so I need a correct date.

So I was going to use text to columns> fixed width and separate the
01/02 from the 2006, then I would concatenate with
(replacing the actual contents with the cell name of course)

="01/02"&"/"&"2006"


But when I do the text to columns, I get the 2006 but I I don't get
01/02.. I get a serial number(even when formatting as text) and then
when I contencate I get garbage


Need help
Thanks
Jerry

There may, likely are, be better ways, but try this.

· Assume "01/022006" is in cell B3
· Enter into C3 =RIGHT(B3,4)
· Enter into D3 =LEFT(B3,5)
· Enter into E3 =CONCATENATE(D3,"/",C3)
· Enter into F3 =VALUE(E3)
(Format F3 as a date)

 

[Jerome Ranch]

Superb Martin
I knew there was a way
Jerry