Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Benjamin Riley
 
Posts: n/a
Default 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
  #3   Report Post  
bigwheel
 
Posts: n/a
Default

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   Report Post  
Earl Kiosterud
 
Posts: n/a
Default

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   Report Post  
Benjamin Riley
 
Posts: n/a
Default

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   Report Post  
Benjamin Riley
 
Posts: n/a
Default

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   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how do I highlite text within a cell (specific characters) tim Excel Discussion (Misc queries) 1 May 20th 05 05:23 AM
On click, copy text into another cell - XL2K Bob the Builder Excel Worksheet Functions 2 March 16th 05 11:03 PM
How do I print all the text in a cell Dorna Tucker Excel Discussion (Misc queries) 4 February 3rd 05 05:23 PM
Link the value to text in a cell, then use that text cell in a for acpharmd Excel Worksheet Functions 6 December 29th 04 03:03 AM
Formatting a cell as "text" in the number catagory. Ed Excel Worksheet Functions 3 December 7th 04 08:12 PM


All times are GMT +1. The time now is 11:49 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"