Sort text Values in Numeric Order

I changed a field type in Access from Number to Text to allow 1a, 1b, etc. 
and now when the data is imported into my Excel workbooks, the order is 
still sorting as text (1, 10, 100, 101, 2, 20, etc.).  Any thought son how 
to get it to sort correctly now?

Thanks! 


0
karlspam (74)
8/7/2005 6:13:14 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
488 Views

Similar Articles

[PageSpeed] 19

Karl,

IN an adjacent column add this formula

=--A1

copy down and then sort by the helper column.

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"Karl Burrows" <karlspam@spam.yourbeacon.com> wrote in message
news:uSRbPcxmFHA.2920@TK2MSFTNGP14.phx.gbl...
> I changed a field type in Access from Number to Text to allow 1a, 1b, etc.
> and now when the data is imported into my Excel workbooks, the order is
> still sorting as text (1, 10, 100, 101, 2, 20, etc.).  Any thought son how
> to get it to sort correctly now?
>
> Thanks!
>
>


0
bob.phillips1 (6510)
8/7/2005 9:36:45 AM
Anything that has a text value (1a, 1b, 1c), gives me a #VALUE error and it 
still wants to sort as a text value.  Any other suggestions?

Thanks!

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message 
news:eFZoHOzmFHA.2484@TK2MSFTNGP15.phx.gbl...
Karl,

IN an adjacent column add this formula

=--A1

copy down and then sort by the helper column.

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"Karl Burrows" <karlspam@spam.yourbeacon.com> wrote in message
news:uSRbPcxmFHA.2920@TK2MSFTNGP14.phx.gbl...
> I changed a field type in Access from Number to Text to allow 1a, 1b, etc.
> and now when the data is imported into my Excel workbooks, the order is
> still sorting as text (1, 10, 100, 101, 2, 20, etc.).  Any thought son how
> to get it to sort correctly now?
>
> Thanks!
>
>



0
karlspam (74)
8/7/2005 7:12:47 PM
If you separate your values into two helper cells--one for the numeric portion
and one for the text portion, you can sort your data based on those two helper
columns.

Depending on what your data looks like, you could use Data|Text to columns or
formulas to parse those values.

You may want to post a representative sample of what your data looks like.

Karl Burrows wrote:
> 
> Anything that has a text value (1a, 1b, 1c), gives me a #VALUE error and it
> still wants to sort as a text value.  Any other suggestions?
> 
> Thanks!
> 
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:eFZoHOzmFHA.2484@TK2MSFTNGP15.phx.gbl...
> Karl,
> 
> IN an adjacent column add this formula
> 
> =--A1
> 
> copy down and then sort by the helper column.
> 
> --
> 
> HTH
> 
> RP
> (remove nothere from the email address if mailing direct)
> 
> "Karl Burrows" <karlspam@spam.yourbeacon.com> wrote in message
> news:uSRbPcxmFHA.2920@TK2MSFTNGP14.phx.gbl...
> > I changed a field type in Access from Number to Text to allow 1a, 1b, etc.
> > and now when the data is imported into my Excel workbooks, the order is
> > still sorting as text (1, 10, 100, 101, 2, 20, etc.).  Any thought son how
> > to get it to sort correctly now?
> >
> > Thanks!
> >
> >

-- 

Dave Peterson
0
petersod (12005)
8/7/2005 8:58:34 PM
Here is what I ended up doing:

Added a function to my VBA module:
    Function CellValue(c) As Double
           CellValue = Val(c)
    End FunctionThen created a column using =CellValue(A1) and copied down 
for all the values in the column and then sorted by this row first, then the 
original column of data.This seemed to work for what I wanted.  Thanks for 
your help!!!
"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message 
news:42F675FA.337E0B00@verizonXSPAM.net...
If you separate your values into two helper cells--one for the numeric 
portion
and one for the text portion, you can sort your data based on those two 
helper
columns.

Depending on what your data looks like, you could use Data|Text to columns 
or
formulas to parse those values.

You may want to post a representative sample of what your data looks like.

Karl Burrows wrote:
>
> Anything that has a text value (1a, 1b, 1c), gives me a #VALUE error and 
> it
> still wants to sort as a text value.  Any other suggestions?
>
> Thanks!
>
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:eFZoHOzmFHA.2484@TK2MSFTNGP15.phx.gbl...
> Karl,
>
> IN an adjacent column add this formula
>
> =--A1
>
> copy down and then sort by the helper column.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
> "Karl Burrows" <karlspam@spam.yourbeacon.com> wrote in message
> news:uSRbPcxmFHA.2920@TK2MSFTNGP14.phx.gbl...
> > I changed a field type in Access from Number to Text to allow 1a, 1b, 
> > etc.
> > and now when the data is imported into my Excel workbooks, the order is
> > still sorting as text (1, 10, 100, 101, 2, 20, etc.).  Any thought son 
> > how
> > to get it to sort correctly now?
> >
> > Thanks!
> >
> >

-- 

Dave Peterson 


0
karlspam (74)
8/8/2005 6:58:53 AM
Reply:

Similar Artilces:

sorting not consistent
Hello All, I hope you can help. My problem is when sorting, Outlook does not behave consistently. Using Outlook 2007, I click on an email. Wanting to sort the emails by that person who sent it, I click the From column header so it will sort alphabetically. Usually Outlook selects the first email in the alphabetical list, not the email I had originally chosen. Sometimes however, it does keep the email I selected. Why does it do this inconsistently? Thanks in advance. Raymond "Raymond" <notanemail@microsoft.com> wrote in message news:%23rZJhywcIHA.4696@TK2MSFTN...

Using Microsoft Help: Add Text Boxes, etc.
Fellow posters: I have MS Excel 2000 (v. 9.0.3821) and need help on working with its map function--the MS Map in my Excel is MapInfo version 8.0. I have never used it before. My eventual goal is to import this map into a MS Word document. For a upcoming report, I'd like to do a few maps of the U.S., where I'd be able to congregate neighboring states together into separate regions and add some text annotation. I'd also like to individually color selected states. After receiving help from a Word group and some hair-pulling, I managed to create a map in MS Map, put it in MS Excel, ...

Translating Dialog text
Hello, Firstly I am new to MFC so apologies for what is I am assuming a very basic question! Summary: I am writing an MFC application which contains one simple dialog and up to 6 message boxes. I need to provide the application in both English and German. The dialog contains a title and static text, both of which will need to be translated. The buttons on the dialog will also need to be translated. The 6 message boxes contain text to be translated, titles to be translated and the button names must also be transated. In total there will only be about 10 strings to be translated. My questions ...

Use Value In An Adjacent Cell
I would like to find the maximum number in a column of numbers and then use the value in the cell one cell above and one cell to the right of the maximum number. Can someone assist me with the formula? The Data: B C D 10/25/2004 57,636 19.039 11/03/2004 58,015 19.627 11/12/2004 58,431 17.774 This formula will return 58,431 (the maximum number in the range) =MAX($C$6:$C$55) Once I find that number I need the value one cell above and one cell to the right (19.627 in this case). ...

Splitting text divided by carriage returns
I have a report in which multiple responses are provided in a single cell. The responses appear on different lines as if these had been added with a carriage return (i.e. as entered using ALT and RETURN). I know that you can normally split cell contents into columns using the Convert Text to Columns Wizard under DATA / TEXT TO COLUMNS and then Delimited, however I don't know what the character or feature would be for soft carriage returns. Any ideas on this or an alternate methodology? Use the sequence ALT + 010 in the other delimiter box. Use the Number pad to enter t...

how do I prevent Excel from spilling text into adjacent columns?
When I have columns that are skinnier than the text they contain, the text spills into the adjacest column(s). How do I prevent this? It makes the spreadsheet very messy looking when I squeeze the columns to get an overview of the spreadsheet. Hi format these cells with word wrap ('format - Cells - alignment') "cp" wrote: > When I have columns that are skinnier than the text they contain, the text > spills into the adjacest column(s). How do I prevent this? It makes the > spreadsheet very messy looking when I squeeze the columns to get an overview > of t...

Compare values in one column against another and display differenc
How can I compare a master part numbers list in one Excel column or worksheet against actual values in a second Excel column or worksheet, and display the missing part numbers that were not in the second column in a new column or worksheet? For Example Part Number Master Part List Missing Parts 12A221315 12A221315 12A221332 12A221316 12A221316 12A221333 12A221317 12A221317 12A221318 12A221318 12A221319 12A221319 12A221320 12A221320 12A221321 12A221321 12A221322 12A221322 12A221323 12A221323 12A221324 12A221324 12A221325 12A221325 12A221326 12A221326 12A221327 12A221327 12A22132...

Need to automate print reports-- for diff values in the query field
I want to email each of my sales reps a snapshot of a Access report that lists their sales invoices for the month (only their sales invoices) Presently I do this by running a macro that runs a query, report & emails a snapshot. I physically enter the value for a field [REP] as a variable for the query. It works fine, but we now have 20 reps so I have to run this macro and type in each of teh 20 rep codes, and I trasnpose. I need to create to automate this so that Access can change the query based on thte [REP] field in a table. I have table #1 that has one record for each rep ...

Entering Transfers and Orders without duplicating?
I have a little problem dealing with transferring items from our warehouse to one of our branches and then entering the same items on an order without duplicating the process and entering each item twice (once on the transfer ticket and once on the order). I want to be able to transfer items from one site to another site and then be able to transfer the entire transfer onto an order without having to re-enter the items onto an order. So in essence, it would sort of be a multifaceted order in which the 1st step is to transfer the order from our warehouse to one of our branches. And ...

How to copy non-contiguous columns to a text file
I would like to copy columns just B and J to a tab-delimited text file. Excel will allow me to select those 2 columns and perform various operations on them (bold, center, etc.). But when I try to copy (Ctrl-C) and then paste into NotePad (Ctrl-V), I get all of the intervening columns. I know I can rearrange the columns, but I'd like to know if there is a way to copy non-contiguous columns. Hi Jennifer, Hold Cntrl and select the B Column Data and hold the Cntrl and select the J Column data also. Now the B and J Column data only will be getting selected. Do Cntrl+C to copy the data and ...

How to get the value of AD user attributes in vbscript?
i want to get the field value of the AD user address attributes in VBscript. just like city, country, etc. how can i get it? thank you very much. Kino, refer to http://gallery.technet.microsoft.com/ScriptCenter/en-us/4d192f4d-2830-4a3e-9352-64a7e696a36e The ones you are interested in are "streetAddress,postOfficeBox,l,st,postalCode,c" hth Marcin "Kino" <Kino@discussions.microsoft.com> wrote in message news:C08BB049-8F6F-462F-B6C8-FB753CEFCA7C@microsoft.com... >i want to get the field value of the AD user address attributes in >VBscript. &...

Find a value and color that cell
Using Min function, I was able to find the next (soonest) date, but not able to color the cell. I used MsgBox to see if any address was assigned to rngFound. Then used rngFound.Cells.Activate to see where the cell was, which resulted way below the list. What did I do wrong? Here is the code: Sub FindNext() Dim myRange As Range Dim answer As String Dim rngFound As Range Set myRange = Worksheets("Sheet5").Range("K2:K80") answer = Application.WorksheetFunction.Min(myRange) Set rngFound = myRange(answer) MsgBox rngFou...

Using DateAdd function with cell values
Both F7 and H7 contain a date yet this sub does not execute the statements after Then.................Please help. Sub GetAmOrDep() Dim TestDate As Date TestDate = DateAdd("m", Range("F7").Value, Range("H7").Value) If TestDate < Range("I7").Value Then Range("K7").Select Selection.Copy Range("M7").Select ActiveSheet.Paste End If End Sub Read help on DateAdd. It is not two dates, but an interval and a date. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "loren.pot...

Export range to text file
I need to export a range from excel to a text file with a delimiter such as a comma or semicolon. I want to be able to have some kind of macro that will check each row and if a row is blank then it will stop there and only export the rows that has data. Which is why I was thinking of using a range, or array. I do have some formulas in the cells so do not want that to show up in the export. Thanks, -- Matt Scheperle mscheperle@gmail.com I have this code that I use to create a csv for later update some values in a system I hope it works Dim ColId As Integer Di...

Ordering Folders in 2007
I have a need to customize folder display order. Right now, they sort alphabetically. I need to order them by importance. So, instead of Arhcive, Policies, Procedures and WINs, I want Policies, Procedures, WINs and Archive. Any suggestions? On Mar 22, 4:47=A0pm, mgauf <mg...@yahoo.com> wrote: > I have a need to customize folder display order. Right now, they sort > alphabetically. I need to order them by importance. So, instead of > Arhcive, Policies, Procedures and WINs, I want Policies, Procedures, > WINs and Archive. Any suggestions? Something similar was recen...

Purchase Order Generator and "Include Orders with No Vendor ID"
GP 8.0, SQL We use Purchase Order Generator. We have a problem with employees not unchecking the box "Include Orders with No Vendor ID" What constitutes an order with no vendor ID? And how can I correct the data? I would like to remove this situation so it doesn't matter whether or not the box is checked. Thanks, Jason ------=_NextPart_0001_63AB11D9 Content-Type: text/plain Content-Transfer-Encoding: 7bit Hi Jason, Thank you for posting to the Great Plains Newsgroups. PO generator looks at the Primary vendor associated to the Item/site combination. If you go to Cards ...

Floating Text Box? Is this possible?
I want to put a disclaimer at the bottom of one of my excel 2002 documents but I don't want to screw up my rows and colums. Is there any way I can create a Floating text box that would work like a paragraph at the bottom of the page? Or if anyone knows a better way to do this. Thanks ohh it's oon the drawing tool bar! >-----Original Message----- >I want to put a disclaimer at the bottom of one of my >excel 2002 documents but I don't want to screw up my rows >and colums. Is there any way I can create a Floating text >box that would work like a paragraph...

Search Row Multiple Values
In row 2 from A to Z I have numbers from 1 to 12. Some numbers appear more than once, sometime 4 times. I want to have an equation that will be able to look through the ro and pic out all of the cells which have the number 4 and return thei column number. Any ideas -- Message posted from http://www.ExcelForum.com One way is to use data>filter>autofilter -- Don Guillett SalesAid Software donaldb@281.com "ianripping >" <<ianripping.157tpr@excelforum-nospam.com> wrote in message news:ianripping.157tpr@excelforum-nospam.com... > In row 2 from A to Z I have nu...

show text after formulas
I have some formulas with text added to the result. For example, I might have a result of: 4.9¢/kWh In the formula cell, I set the numeric format to number, one digit after decimal. How do I retain the format when I have the numeric set to show commas? When I want to show a result of: 2,345,899 kWh Excel gives me a result of: 2345899 kWH. Why is it ignoring my format when I introduce text? A formula example when this happens might be: =ROUND(((G40/H45)*D19*100 ),0)&"kWh" help! this is driving me crazy!! I want to show units in the same cell, but these n...

Paste Values in linked Cells Only
Goodmorning Everyone! I have a worksheet within a workbook that contains 1500 rows of data over 14 columns (the 12 months, plus to summary columns) - called the "Year to Date" sheet. The YTD sheet has links to other worksheets within the same workbook. This workbook is used as a roll forward workbook. The YTD sheet does not change, but all other worksheets in the workbook get updated with the current months data. In preparing for a new month, I have written a macro that cleans up a lot of the data from the last month. The problem is that I want the YTD worksheet...

Converting Text to Date
Hi, Is anybody can tell me, how to convert from text data to Date?. I have a table imported from Text file, text file only recognize text & number only. One of the field (we call: TxtDate) they have is '20070515" (yyyymmdd) has to be in date format. How can I convert it to our regular format (mm/dd/yy), so I can calculate it? I tried using Format(mid(TXTDate,5,2)&"/"mid(7,2)&"/"mid(3,2),"mm/dd/yy"). It works, but I still can't use it as a date, so I can't put a parameter on it. Thanks in advance KF -- Message posted via AccessMo...

SumProduct
Hi All, I am trying to use the following formula to count the number of entries that are not Closed:- =SUMPRODUCT((Val_RSK_Status<>"Closed")*(Val_RSK_Owner="Jim")) It produces a result, but not the right one - have I got the Syntex for Not Equal to Right? Thanks in advance Andy Your syntax is fine. What is the error? Your formula will count blanks as <>"Closed". If you don't want that, one way is: =SUMPRODUCT(--(Val_RSK_Status<>"Closed"), --(Val_RSK_Status<>""), --(Val_RSK_Owner="Jim")) In...

Help! Item with Back Order Qty not picking up on Purchase Advice R
Our employee in charge of purchasing came to me this morning with a dilemma. There is an item with a back order qty of 1 but the item never showed up on the purchase advice report. This is an item we don't want to stock so there is no min or max set up for it. In the Item Resource Planning Maintenance window here are the settings for this item: Order Policy: Not Planned Fixed Order Qty: 0 Order Point Qty: 0 Order-Up-To Level: 0 Replenishment Method: Buy all the other fields are set to 0 with the exception of the "Order Qty Modifiers" section which has a "Multiple&...

Excel
Hey all! k, a quick question. i have one column - it is email address - i need to pull from this list all email address that are @aol.com and @yahoo.com and put them into their own list - how can i go about doing this? Mathiau. I think you may use Find() function to find out the position of "@" first, then use Mid() function to get the following text, then put it to a new column. HTH "Mathiau" <mathiau@quickring.com> �b�l�� news:OC6w8wGbDHA.2072@TK2MSFTNGP10.phx.gbl �����g... > Hey all! > > > k, a quick question. > > i have one column - it ...

Urgent
Hi, I urgently need to Change the user name order in the CRM for existing users. Eg (First, Last) (First, Last) (Last, First) (First, Last) (Last, Fisrt) How do i change all the names currently in the system to be arranged from (Last, First) without disabling the users and creating new users. Urgently awaiting a response. Jefferson Run the following statement in SQL Query Analyser on your _MSCRM database UPDATE SystemUserBase SET FullName = FirstName + ',' + LastName It is unsupported but I have run similar statements many times successfully. Back up first would be wise &...