Unique Number Formatting 1

[mattalin]

Wonder if the following is possible in Excel:

Let's say I have the following numbers (which are generated when I export from an internal query s/w program, Brio, into Excel):

1
101
10102
10103
1010301
1010302
102

I would like to be able to automatically convert each of the above to the following:

.01
.01.01
.01.01.02
.01.01.03
.01.01.03.01
.01.01.03.02
.01.02

 

[DaleWatson123321]

Matt,

I believe I can provide a solution for you.

It will require a series of formulas that I should ideally email you. If you'd like to email me, I'll send a file via return email.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[mattalin]

Thanks Dale! The solution you provided worked perfectly.

For anyone that might have a similar problem, here's how Dale solved it:

1) Created a "length" formula to determine the length of the number.

2) Created several "pos_x" formulas for proper position and formatting, based on the length of the number required.

3) Created a "choose1" formula to determine which position (pos_1, pos_2, etc) to use, based on the length formula.

4) Finally created "choose2" formula that references the value of "choose1" to determine which "pos_" column to use.

Thanks again!!