convert to 2 digit number

I have a concatenate formula that pulls from a few different cells, then I sort by the column that formula is in. The problem is that one of the cells pulled from contains numbers from 1-16. Because concatenate converts the numbers to text, it sorts 10 before 2. So what I need to do is force the number pulled to appear as 2 digits in the result of the concatenate formula. Is there a way to do this?

Thanks,
Justin

What fomula are you using now?

You could use a TEXT function to make 2 into 02, e.g. =TEXT(your_formula,"00").....or if you only have digits in the number perhaps convert to numeric with

=(your_formula)+0

The Text(???,"00") worked. All I had to do was insert it around one of the cells I was concatenating and it returned the value I wanted. I'll have to remember to put my code in next time. How do you put it in that box, Ron?

Thanks,
Justin

Thanks. That helps a lot.

This post gave me a clue as to what to do, but it was not the final answer . . . so I wanted to add to it, because I finally figured it out! You give the number format with the zeros, then you can sort!

This is for my hardware spreadsheet (nuts and bolts, wood screws, machine screws):

Format the Size, Threads, and Length columns as a Fraction up to 2 digits . . . then in the CONCATENATE formula, for sorting purposes, convert it to this format: = TEXT(K4,"00.00000")
In my case here, there shouldn't be more than 2 digits before the decimal, and 5 digits past the decimal is probably good enough.
If you don't do this, then, for example, it will group any numbers starting with "1" all together when you sort, like: 1/2, 1, 10 . . . or: 2, 20, 200

Previously, I had the column formatted as General. Integers were in the General format, and when I had to enter fractions, I changed the cell format to a Fraction up to 2 digits - these were all in the same column - and I don't think they were being sorted properly - as described above.

It makes it simpler, also, to have the whole column as the same number format.

So now . . .

To sort by Size
=CONCATENATE(A4,"...",H4,"...",TEXT(I4,"00.00000"),"...",TEXT(K4,"00.00000"),"...",TEXT(J4,"00.00000"),"...",M4,"...",L4,"...",N4,"...",O4,"...",P4,"...",C4,"...",D4)

To sort by Length
=CONCATENATE(A4,"...",TEXT(K4,"00.00000"),"...",H4,"...",TEXT(I4,"00.00000"),"...",TEXT(J4,"00.00000"),"...",M4,"...",L4,"...",N4,"...",O4,"...",P4,"...",C4,"...",D4)

Hi all: This is my first post on this site:

I work in a transportation field and am responsible for rail schedules. I am trying to add a fixed number of minutes to the row above. The problem is that the display formats of time that are available (from the format menu) don't match the industry standard (6:21A, 10:14P). Also, we sometimes add text in front of the time which have different meanings to our crews (S 6:21A, L 10:14P). Due to these anomalies, I have discovered that it is easier to keep things in "general" or "text" format.

Unfortunately this leads to a few complications when adding a fixed number of minutes to the cell above. So far, I have the following formula. For Cell D122, I use the following: =IF(LEN(D121)=5,LEFT(D121,2)&(MID(D121,3,2)+3)&(RIGHT(D121,1)),LEFT(D121,3)&(MID(D121,4,2)+3)&(RIGHT(D121,1))). The reason for the if statement is whether the hours is a one digit number or a 2 digit number.

this formula works great, except for when the minutes (positions 3/4 or 4/5) are between 00 and 07, because when I add the 2 minutes to these numbers, I end up with a 1-digit number between 2 and 9.

For example, adding 2 to the 3rd and 4th characters of 6:01A turns unto 6:3A, when're I want 6:03A.

Is there any way to force the results to be a 2 digit number?

One solution is to add a hidden row in-between with the results of the addition, force it into a 2-digit number using the formatter, and then joining the pieces together, but I was hoping to just add a string into the middle of my formula.