jtencha

asked on

# Custom number ("Tape measure" )format for Excel 2003

I want to use Excel 2003 to draft up a preliminary floor plan for a room. I want to add dimensions on the

length and width of the perimeter of the layout such that the numbers are displayed as follows (in inches):

0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 1.0, 1.1, 1.2, 1.3, 1.4, 1.5, 1.6, 1.7, 1.8, 1.9, 1.10, 1.11, 2.0, 2.1, 2.2.......3.0...etc

As you can see, for every 12 inches, the number resets back to the next whole one foot measurement.

I need to be able to enter the first number, and then have the capability to click and drag the numbers across the screen so that excel will automatically fill the numbers in this format for me.

thanks

length and width of the perimeter of the layout such that the numbers are displayed as follows (in inches):

0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 1.0, 1.1, 1.2, 1.3, 1.4, 1.5, 1.6, 1.7, 1.8, 1.9, 1.10, 1.11, 2.0, 2.1, 2.2.......3.0...etc

As you can see, for every 12 inches, the number resets back to the next whole one foot measurement.

I need to be able to enter the first number, and then have the capability to click and drag the numbers across the screen so that excel will automatically fill the numbers in this format for me.

thanks

ASKER CERTIFIED SOLUTION

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

I'm not sure if this will be useful, but if you have the numbers in a column or row (maybe hidden on the sheet, you could use the following formula to translate the number to the style you like.

=IF(QUOTIENT(B10,12)<1, B10, QUOTIENT(B10,12) & "." & MOD(B10,12))

This will turn the cell b10 into the following:

b10=25 then formula cell = 2.1

b10=11 then formula = 11

b10=13 then formula = 1.1

=IF(QUOTIENT(B10,12)<1, B10, QUOTIENT(B10,12) & "." & MOD(B10,12))

This will turn the cell b10 into the following:

b10=25 then formula cell = 2.1

b10=11 then formula = 11

b10=13 then formula = 1.1

I can produce the exact series requested with this formula:

=SUBSTITUTE(SUBSTITUTE(TEXT(A1/12,"# 0/12"),"/12","")," ",".")

I can also produce a series with a leading decimal point for values less than 1 foot with:

=SUBSTITUTE(SUBSTITUTE(TEXT(A1/12,") 0/12"),"/12","")," ",".")

Results of the two formulas are shown below:

Inches 0 1 2 3 4 5 6 7 8 9 10 11 12 13 21 22 23 24 25

Formula 1 0 1 2 3 4 5 6 7 8 9 10 11 1.0 1.1 1.9 1.10 1.11 2.0 2.1

Formula 2 0.0 0.1 0.2 0.3 0.4 0.5 0.6 0.7 0.8 0.9 0.10 0.11 1.0 1.1 1.9 1.10 1.11 2.0 2.1

Brad

=SUBSTITUTE(SUBSTITUTE(TEX

I can also produce a series with a leading decimal point for values less than 1 foot with:

=SUBSTITUTE(SUBSTITUTE(TEX

Results of the two formulas are shown below:

Inches 0 1 2 3 4 5 6 7 8 9 10 11 12 13 21 22 23 24 25

Formula 1 0 1 2 3 4 5 6 7 8 9 10 11 1.0 1.1 1.9 1.10 1.11 2.0 2.1

Formula 2 0.0 0.1 0.2 0.3 0.4 0.5 0.6 0.7 0.8 0.9 0.10 0.11 1.0 1.1 1.9 1.10 1.11 2.0 2.1

Brad

What Brad provided is a formula to convert inches to the inches/feet value you want. But it doesn't work as a "drag" solution.

This formula works as a sequence generator. To use it, put the first measurement into a cell after formatting that cell as text. Place this formula in an adjacent cell:

=SUBSTITUTE(SUBSTITUTE(TEXT(IF(ISERROR(FIND(".",A1)),(A1+1)/12,LEFT(A1,FIND(".",A1)-1)+(MID(A1,FIND(".",A1)+1,2)+1)/12),"# 0/12"),"/12","")," ",".")

changing the reference "A1" to the cell containing the first measurement. Then drag or copy the formula as far down or to the right as desired.

Kevin

This formula works as a sequence generator. To use it, put the first measurement into a cell after formatting that cell as text. Place this formula in an adjacent cell:

=SUBSTITUTE(SUBSTITUTE(TEX

changing the reference "A1" to the cell containing the first measurement. Then drag or copy the formula as far down or to the right as desired.

Kevin

To ensure this question does not get deleted, I'm objecting to the cleanup action now that two additional possible solutions have been posted.

Kevin

Kevin

1 1/12

1 2/12

1 3/12

...

2

2 1/12

Use the custom number format "# ??/12" to do this.

Kevin