How to insert text into a cell

B

Benjamin Riley

I have a sheet full of dates exported from an accounting software package.
The dates are in the following format yyyymmdd as in 19810305 for March 5,
1981. What I need to do is change the look of the cell from 19810305 to
03/05/1981. Can someone help me with this.

P.S. I tried to format the cells as a date but it just gives me a bunch of
#### crap.

Thanks


BEN
 
R

Roger Govier

Hi Benjamin

One way, with your date value in A1 would be
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
Change reference accordingly and copy down the sheet.
 
B

bigwheel

using =MID(A1,5,2)&"/"&RIGHT(A1,2)&"/"&LEFT(A1,4) will change the look of
the cell
 
E

Earl Kiosterud

Benjamin,

yyyymmdd isn't a date format Excel recognizes. So it just considers
19810305 a number (or maybe text, if it got imported that way). If one was
in A2, you could use:
=DATE(LEFT(A3,4),MID(A3,5,2),RIGHT(A3,2))

This would yield a bona-fide date, which you could format in many ways with
Format - Cells - Number - Date tab (or Custom, and make your own format
code, mm/dd/yyyy in your example).
 
B

Benjamin Riley

Thanks for the help this works great

Roger Govier said:
Hi Benjamin

One way, with your date value in A1 would be
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
Change reference accordingly and copy down the sheet.
Click to expand...
 
D

Dave Peterson

If your dates are in a nice column, select that range and do data|Text to
columns.

Choose fixed width
remove any lines that excel may have guessed
choose ymd as the date format
widen the column to see the date.
format it the way you like (mm/dd/yyyy)
 
Top