Excel Nightmare

Discussion in 'IT & Internet' started by Merchant UK, Dec 16, 2014.

Thread Status:
Not open for further replies.
  1. Merchant UK

    Merchant UK UKBF Ace Full Member - Verified Business

    2,419 592
    OK guys heres a quick one for you, only i've spent days trying to figure it out with no luck

    I have a spread sheet with Numbers and letters and have been trying to get my numbers in order

    but i'm getting this.....

    F1
    F10
    F11
    F19
    F20
    F21
    F22
    F23
    F25
    F26
    F4
    F5
    F6
    F7
    F8

    How can i fo it so when i sort it, i will show F1, F2, etc and not in the order above??

    Thanks guys
     
    Posted: Dec 16, 2014 By: Merchant UK Member since: Aug 15, 2010
    #1
  2. titanium

    titanium UKBF Regular Free Member

    242 31
    Posted: Dec 16, 2014 By: titanium Member since: Mar 30, 2013
    #2
  3. Hash and Bash

    Hash and Bash UKBF Regular Free Member

    314 62
    You could change your data input method to include a "0" before any single digit numeral.

    i.e. input F01 for F1 etc - that way sort will work correctly.
     
    Posted: Dec 16, 2014 By: Hash and Bash Member since: Nov 8, 2012
    #3
  4. Jeff FV

    Jeff FV UKBF Big Shot Staff Member

    3,634 1,720
    One way:

    Create a new column next to the F0, F10 column etc.

    Use "Text to columns" (found under the "Data" tab) to split the column into 2: one which will just have F in it, the other the number.

    *optional step: create another new column and concatenate the previous two cells if you need the data back in the F0, F1, F10 format.

    Now select all cells and sort by the number column you have created.

    HTH

    J
     
    Posted: Dec 16, 2014 By: Jeff FV Member since: Jan 10, 2009
    #4
  5. Merchant UK

    Merchant UK UKBF Ace Full Member - Verified Business

    2,419 592
    You know what! Thats exactly what i did and it worked a treat, I've been reading pages online on formatting changing letters into numbers and all that Poop, when all i had to do was add a zero infront. So easy, so thanks for thank
     
    Posted: Dec 16, 2014 By: Merchant UK Member since: Aug 15, 2010
    #5
  6. Hash and Bash

    Hash and Bash UKBF Regular Free Member

    314 62
    Sometimes there are simple solutions ;)

    If you already have your spreadsheet populated with data such as F1, you could use a find and replace to substitute F01 in its place (and for all similar single digit numbers).
     
    Posted: Dec 16, 2014 By: Hash and Bash Member since: Nov 8, 2012
    #6
Thread Status:
Not open for further replies.