Insert a Seperator into a number string.
 

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.

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)


"DraCo" wrote in message
...
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.

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.

DraCo wrote:

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.

--

Dave Peterson

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
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"DraCo" wrote in message ...
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.