Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

another excel sort

Posted on 2011-10-07
2
Medium Priority
?
251 Views
Last Modified: 2012-05-12
Need some more help... please
If i have a column with values such as
R9
R8
R1
R10
Q10
Q1

How can I  force a 0 infront of the single digits.
I need to do a sort.....
Right now it I sort it does

R50
R5
.....

but if I was to stick a 0 in front of the 5 it would work just fine.
R05
R50

Please help... I'm using excel 2007
0
Comment
Question by:MrMay
2 Comments
 
LVL 50

Accepted Solution

by:
barry houdini earned 2000 total points
ID: 36932604
If data is in A2 down try this formula in B2 to convert

=LEFT(A2)&TEXT(MID(A2,2,9),"00")

regards, barry
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 36933732
MrMay,

Will there ALWAYS be just one letter in the front?  Will there ALWAYS be just one or two digits following that one letter?  If so, barry's approach above will work just fine although you do need to fix the LEFT expression to LEFT(A2,1).

If there is any possibility that your data can be anything else, please indicate that.

Patrick
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background Information Recently I have fixed file server permission issues for one of my client. The client has 1800 users and one Windows Server 2008 R2 domain joined file server with 12 TB of data, 250+ shared folders and the folder structure i…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question