Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seed numbers for random number generation, uniform distribution | Excel Discussion (Misc queries) | |||
trim a string by specific number of characters | Excel Discussion (Misc queries) | |||
How do I insert 2 decimal places to a number in cell? | Excel Worksheet Functions | |||
insert picture | Excel Discussion (Misc queries) | |||
Insert value of a cell as a filename | Excel Worksheet Functions |