Forum Discussion
Headaking
Jan 30, 2019Copper Contributor
Giant stupid numbers
My county assessor provided a CSV download of tax info; and one data field is a parcel number. Here is one 17-digit example:
366629301057000009 |
what I want to do is format it like this: 36-66-29-301-057.000-009
complete with dashes and the decimal point.
I have this value in cell B3.
I've tried cell c3=Text(b3,"proper format described") and it does not work.
I've tried to copy B into C and do a column format, using "Format Cells, Custom Format" and this also does not work.
The =Text function actually starts to work, but get's lost and doesn't format so many digits and it loses the trailing 9 altogether.
The original entry for this number in the CSV download appears to just be text; as I cannot reformat it as a number with comma separators or decimals.
I am using Office Professional 2007
Another way is
=REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(B2,16,0,"-"),13,0,"."),10,0,"-"),7,0,"-"),5,0,"-"),3,0,"-")
Please see attached
- JKPieterseSilver ContributorAll I can come up with is this tedious formula:
=LEFT(A1,2)&"-"&MID(A1,3,2)&"-"&MID(A1,5,2)&"-"&MID(A1,7,3)&"-"&MID(A1,10,3)&"."&MID(A1,13,3)&"-"&RIGHT(A1,3)- HeadakingCopper Contributor
This works great, and is not really tedious if I can make the solution work for about 1400 records in less than one minute!
Thanks very much for a solution as well as my introduction to a new formula methodology.
- JKPieterseSilver ContributorYou're welcome!