# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  convert to 2 digit number

## pilotwings64

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

----------


## daddylonglegs

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

----------


## Ron Coderre

You really need to post your formula.
Absent that, here's a guess...

With
A1: 2
B1: Alpha

This formula assures that A1, when concatenated, has 2 digits:



```
Please Login or Register  to view this content.
```


In the above example, the formula returns: 02Alpha

Is that something you can work with?

----------


## pilotwings64

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

----------


## Ron Coderre

To wrap a formula (or vba code) in CODE tags...

 Select the specific text in the input window
 Click the [#] button just above the input window
   (...it's on the right side of that mess of buttons   :Smilie:    )
    and [CODE] tags will enclose the selection

You can click the [Preview Post] button to make sure everything looks
right before you submit.

----------


## pilotwings64

Thanks.  That helps a lot.

----------


## slack7639

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)

----------


## phkc070408

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.

----------


## AliGW

*Administrative Note:*

Welcome to the forum.  :Smilie: 

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original. Please start a new thread - See Forum rule #4

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

----------

