Insert a Seperator into a number string.

[Guest]

How do I insert a seperator (/) into a number string (20050822) to format a
date column? This number string is brought in from an outside source and
cannot be manipulated by the program (AccPac) generating the string.

 

[Bob Phillips]

In an adjacent cell, add

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

copy down and format as your preferred date style

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Dave Peterson]

You could convert that column to real dates and format that column anyway you
want:

Select the range (whole column??)
data|text to columns
fixed width
remove any lines that excel may have guessed.
and choose ymd as the date format.
Then finish up

Then Format|Cells|number tab|choose your nice date format.

 

[David McRitchie]

Would suggest you convert them to Excel dates. The following
macro will convert a text string of numbers or a number of 8 digits
to Excel date format. The the formatting will match the short date
format in your regional settings (control panel).

Sub Fixmmddyyyy()
Dim cell As Range
Selection.NumberFormat = "mm/dd/yyyy"
On Error Resume Next
For Each cell In Selection
If Len(cell) = 8 Then
cell.Value = DateSerial(Right(cell.Value, 4), _
Left(cell.Value, 2), Mid(cell.Value, 3, 2))
End If
Next cell
End Sub

Test on a copy of your worksheet. Should be safe to reuse
on a column that is already converted because the length will
no longer be 8.

If not familiar with installing and using macros see
http://www.mvps.org/dmcritchie/excel/getstarted.htm

More information on Date and Time in
http://www.mvps.org/dmcritchie/excel/datetime.htm