Home

Giant stupid numbers

Headaking
New Contributor

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

4 Replies
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)

Another way is

=REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(B2,16,0,"-"),13,0,"."),10,0,"-"),7,0,"-"),5,0,"-"),3,0,"-")

image.png

 

Please see attached

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.

You're welcome!
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies