help! - convert zip codes stored as text to special/zip code format

[dbwiz]

I have a column from an export that contains 5 digit zip codes. The numbers are stored as text. Although I formatted the column and selected Special/Zip Code, I cannot get the cells to convert to a date without actually having to click on each cell - THEN it will change. And Data/Text to Columns doesn't seem to have the option. (that only works when converting General or something else to Text).

(Am doing this because I have a separate exported report that has the zip codes in special/date format. If I convert them to text (in order to match the first report), the leading zeros go away. So my VLOOKUP will not work unless both "types" are the same. Any help would be greatly appreciated!

 

[jreedich]

I hope I am reading your question properly so here goes.

<TABLE style="WIDTH: 78pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=104 border=0><COLGROUP><COL style="WIDTH: 39pt" span=2 width=52><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 39pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=52 height=20>A</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 39pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=52>B</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>08032</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">08032</TD></TR></TBODY></TABLE>

Column A is formatted as special zip code.
Column B is formatted as text
The formula in column B is =TEXT(RC[-1],"00000")

You can then copy and paste special as values.

If I have completely misunderstood your problem please forgive. The typo "Date" through me for a minute.

 

[dbwiz]

thanks - and sorry about the typo