Function Help Please

[AJ]

Tuesday, April 14, 2009

That data above, once imported from a tab delimited file, is interpreted by
Excel as General format. This is a couple of years worth of this data.

I'd like to shorten the date while removing the day from the beginning.
Highlighting the cell then manually changing the format to Date does
nothing.

I think my only option is to use a function that keeps all data after the
first space.

Thanks,

AJ

 

[Rick Rothstein]

Select the column with those "dates" in it, then click Edit/Replace on
Excel's menu bar... type these four characters (without the quote marks)
"*y, " (asterisk, letter "y", comma, space) into the "Find what" field and
leave the "Replace with" field empty, then click the "Replace All"
button.... your cells should not contain real dates that you can format in
anyway that you want.

 

[Rick Rothstein]

....your cells should not contain real dates....

.....Your cells should NOW contain real dates....

 

[AJ]

Well that's quite clever Rick. Thank you.

AJ

Select the column with those "dates" in it, then click Edit/Replace on
Excel's menu bar... type these four characters (without the quote marks)
"*y, " (asterisk, letter "y", comma, space) into the "Find what" field and
leave the "Replace with" field empty, then click the "Replace All"
button.... your cells should not contain real dates that you can format in
anyway that you want.

 

[Vince Bernhardt]

I have to agree, that's brilliant and clever.

Only works with days that end in "y" though.

(No, really, that was brilliant. I would've ended up doing string
manipulation.)