Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to insert text into a cell
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 |
#2
|
|||
|
|||
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. -- Regards Roger Govier "Benjamin Riley" <Benjamin wrote in message ... 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 |
#3
|
|||
|
|||
using =MID(A1,5,2)&"/"&RIGHT(A1,2)&"/"&LEFT(A1,4) will change the look of
the cell "Benjamin Riley" wrote: 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 |
#4
|
|||
|
|||
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). -- Earl Kiosterud www.smokeylake.com/ ------------------------------------------- "Benjamin Riley" <Benjamin wrote in message ... 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 |
#5
|
|||
|
|||
Thanks for the help this works great
"Roger Govier" wrote: 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. -- Regards Roger Govier "Benjamin Riley" <Benjamin wrote in message ... 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 |
#6
|
|||
|
|||
Thanks for the help. This works great.
"bigwheel" wrote: using =MID(A1,5,2)&"/"&RIGHT(A1,2)&"/"&LEFT(A1,4) will change the look of the cell "Benjamin Riley" wrote: 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 |
#7
|
|||
|
|||
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) Benjamin Riley wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I highlite text within a cell (specific characters) | Excel Discussion (Misc queries) | |||
On click, copy text into another cell - XL2K | Excel Worksheet Functions | |||
How do I print all the text in a cell | Excel Discussion (Misc queries) | |||
Link the value to text in a cell, then use that text cell in a for | Excel Worksheet Functions | |||
Formatting a cell as "text" in the number catagory. | Excel Worksheet Functions |