• 408K Members
• 7,788 Online
• 465K Conversations

New 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

4 Replies

# Re: Giant stupid numbers

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)

# Re: Giant stupid numbers

Another way is

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

# Re: Giant stupid numbers

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.

# Re: Giant stupid numbers

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