Forum Discussion

Headaking's avatar
Headaking
Copper Contributor
Jan 30, 2019

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

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    All 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)
    • Headaking's avatar
      Headaking
      Copper 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.

Resources