convert to 2 digit number https://www.excelforum.com/excel-formulas-and-...
User Name Password Log in Forgotten Your Password? Help Register
Remember Me?
Forum What's New? Members List Calendar Forum Rules Dashboard Commercial Services
Today's Posts FAQ Calendar Community Forum Actions Quick Links
Advanced Search
Forum Microsoft Oce Application Help - Excel Help forum Excel Formulas & Functions convert to 2 digit number
Get hands-on experience with our
always-free product tier.
TRY IT FREE
To get replies by our experts at nominal charges, follow this link to buy points and post your thread in our Commercial Services forum! Here is the
FAQ for this forum.
Results 1 to 7 of 7
+ Reply to Thread
convert to 2 digit number
LinkBack Thread Tools Rate This Thread Display
08-28-2008,01:13 PM #1
pilotwings64
convert to 2 digit number
Registered User
I have a concatenate formula that pulls from a few different cells, then
Join Date: 01-07-2008 I sort by the column that formula is in. The problem is that one of the
Posts: 65 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
Register To Reply
08-28-2008,01:28 PM #2
daddylonglegs What fomula are you using now?
Forum Moderator
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
Join Date: 01-14-2006
Location: England
MS-O Ver: 2007+2010+2013
Posts: 14,227
Register To Reply
08-28-2008,01:30 PM #3
Ron Coderre
convert to 2 digit number
Cheeky Forum Moderator
You really need to post your formula.
Absent that, here's a guess...
With
A1: 2
B1: Alpha
Join Date:
This formula assures that A1, when concatenated, has 2 digits:
1 of 3 08/04/2017 07:07 AM
convert to 2 digit number https://www.excelforum.com/excel-formulas-and-...
03-22-2005
Location: Boston,
Massachusetts Please Login or Register to view this content.
MS-O Ver: 2010, 2013
Posts: 6,794
In the above example, the formula returns: 02Alpha
Is that something you can work with?
Ron
Microsoft MVP - Excel (Oct 2006 - Sep 2015)
Click here to see the Forum Rules
Register To Reply
08-28-2008,01:39 PM #4
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?
Registered User
Join Date: 01-07-2008
Thanks,
Posts: 65
Justin
Register To Reply
08-28-2008,02:10 PM #5
Ron Coderre
How to wrap formulas or code in CODE tags
Cheeky Forum Moderator
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 )
and [CODE] tags will enclose the selection
Join Date: 03-22-2005 You can click the [Preview Post] button to make sure everything looks
Location: Boston, right before you submit.
Massachusetts
MS-O Ver: 2010, 2013
Posts: 6,794
Register To Reply
08-28-2008,02:25 PM #6
pilotwings64 Thanks. That helps a lot.
Registered User
Join Date: 01-07-2008
Posts: 65
Register To Reply
06-05-2012,04:55 PM #7
slack7639
Re: convert to 2 digit number
Registered User
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
Join Date: 05-24-2011 figured it out! You give the number format with the zeros, then you can sort!
Location: Cleveland, Ohio
MS-O Ver: Excel 2010 This is for my hardware spreadsheet (nuts and bolts, wood screws, machine screws):
Posts: 35
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.
2 of 3 08/04/2017 07:07 AM
convert to 2 digit number https://www.excelforum.com/excel-formulas-and-...
So now . . .
To sort by Size
=CONCATENATE(A4,"...",H4,"...",TEXT(I4,"00.00000"),"...",TEXT(K4,"00.00000"),"...",TEXT(J4,"00.00000"),"..."
To sort by Length
=CONCATENATE(A4,"...",TEXT(K4,"00.00000"),"...",H4,"...",TEXT(I4,"00.00000"),"...",TEXT(J4,"00.00000"),"..."
Register To Reply
+ Reply to Thread
INDIRECT Function for Dashboard workbook Use DATEDIF function in Excel 2007 Color Dynamic Cascading
Two-Dimensional getting Age changes when I copy a Drop-Downs Using
Lookup worksheet to another... INDEX & MATCH
Previous Thread | Next Thread
Thread Information
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks Posting Permissions
Digg You may not post new threads BB code is On
del.icio.us You may not post replies Smilies are On
You may not post attachments [IMG] code is O
StumbleUpon You may not edit your posts HTML code is O
Google Trackbacks are O
Pingbacks are O
Refbacks are On
Forum Rules
-- vB4 Default Style Contact Us ExcelForum.com Archive Top
All times are GMT -4. The time now is 07:07 AM.
Search Engine Friendly URLs by vBSEO 3.6.0 RC 1
3 of 3 08/04/2017 07:07 AM
