Jan 30 2019 10:32 AM
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
Jan 30 2019 11:36 AM
Jan 31 2019 02:04 AM - edited Jan 31 2019 02:06 AM
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
Jan 31 2019 04:34 AM
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.