I have a column of cells within Excel with different values, an example of
the text is like 20060602. I want them to be in the format 2006-06-02. Can
you suggest a formula to use

Hi RC,

One way is to format the cells as custom 0000"-"00"-"00

HTH
Martin

This appears to work and the cell appears as you say 2006-06-02. However, in
the formula bar the cell still reads 20060602. I cannot permanently change
the look even by using the cut n paste special (value) to another cell.
Any other ideas please

Thanks

Ron

"MartinW" wrote:

Hi RC,

One way is to format the cells as custom 0000"-"00"-"00

HTH
Martin

=concatenate(mid(a1;1;4);"-";mid(a1;5;2);"-";mid(a1;7;2))


--
ggl
------------------------------------------------------------------------
ggl's Profile: http://www.excelforum.com/member.php...o&userid=35591
View this thread: http://www.excelforum.com/showthread...hreadid=554472

"RC" wrote:
.. However, in the formula bar the cell still reads 20060602.
I cannot permanently change the look even
by using the cut n paste special (value) to another cell.

With source data (20060602) assumed representative and running in A1 down

Try in B1:
=LEFT(A1,4)&"-"&MID(A1,5,2)&"-"&RIGHT(A1,2)
Copy down
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Just a thought .. if what you're trying to do is get Excel to recognize the
source text data as real dates (the data looks like dates), then we could try
this ..

Select col A
Click Data Text to Columns
Click Next Next to proceed to step 3 of the wizard
In step 3,
check "Date" then select the format: YMD or YDM from the droplist,
click Finish
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Hi again RC

Maybe try this in B2 with your date in A1.

=LEFT(A1,4)&"-"&MID(A1,5,2)&"-"&RIGHT(A1,2)

HTH
Martin

On Thu, 22 Jun 2006 03:28:01 -0700, RC wrote:

I have a column of cells within Excel with different values, an example of
the text is like 20060602. I want them to be in the format 2006-06-02. Can
you suggest a formula to use


=TEXT(A3,"0000-00-00")

However, what do you want to do with the result? This will not be interpreted
as a date by Excel.


--ron

Insert a column next to the data with A1 being the cell with the unformatted
data... enter this formula...
=CONCATENATE(LEFT(A1,4),"-",MID(A1,5,2),"-",RIGHT(A1,2)) --
then select the formula column and copy, then paste as values... then delete
the unformatted column
~Christine, CPA


"Ron Rosenfeld" wrote:

On Thu, 22 Jun 2006 03:28:01 -0700, RC wrote:

I have a column of cells within Excel with different values, an example of
the text is like 20060602. I want them to be in the format 2006-06-02. Can
you suggest a formula to use


=TEXT(A3,"0000-00-00")

However, what do you want to do with the result? This will not be interpreted
as a date by Excel.


--ron