Add Dashes within aText String

R

RC

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
 
R

RC

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
 
M

Max

RC said:
.. 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.
Click to expand...

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
 
M

Max

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
 
M

MartinW

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
 
R

Ron Rosenfeld

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
Click to expand...


=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
 
C

Christine

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
 
Top