+ Reply to Thread
Results 1 to 4 of 4

List of Numbers to be formatted 1a, 1. 3, 3a 2, 2a etc

  1. #1
    Registered User
    Join Date
    11-28-2020
    Location
    Stockport, England
    MS-Off Ver
    MS Office 365
    Posts
    2

    List of Numbers to be formatted 1a, 1. 3, 3a 2, 2a etc

    Hi
    I have a spreadsheet with addresses on and need to format the numbers in order as below. I need to seperate them by odds and evens as well

    I have a list as below

    42A
    40
    39
    39a
    38A
    37A
    35
    35A
    31A
    30A
    29A
    and I need them to be in some seperate order like below
    all the odds from 42A, 40, 38A 30a ETC..
    all the same with the ODDS s from 2a to 42a

    I dont want the odds and evens together
    Any help to a novice is much appreciated
    Example sheet attached
    Attached Files Attached Files
    Last edited by Raver62; 11-28-2020 at 03:27 PM. Reason: Adding A Sheet as an example

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,695

    Re: List of Numbers to be formatted 1a, 1. 3, 3a 2, 2a etc

    Welcome to the forum.

    So you want the odd numbers to be in reverse order and the even numbers in increasing order? Are you a postman working up and down a street by any chance?

    It would help if you attached a sample Excel workbook, so we can see what you are starting with (and so that we don't have to type those values in to a blank file before being able to work on a solution). Instructions on how to do this are given in the yellow banner at the top of the screen.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    11-28-2020
    Location
    Stockport, England
    MS-Off Ver
    MS Office 365
    Posts
    2

    Re: List of Numbers to be formatted 1a, 1. 3, 3a 2, 2a etc

    Pete

    Ok I've attached a sheet

    Thanks
    Dave

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,525

    Re: List of Numbers to be formatted 1a, 1. 3, 3a 2, 2a etc

    This proposal employs a helper column (C) which may be moved and/or hidden for aesthetic purposes.
    The helper column is populated using: =IF(MOD(--LEFT(A3,IFERROR(SEARCH("A",A3)-1,LEN(A3))),2)=0,"Even","")
    The descending list of odd numbered addresses is populated using: =IFERROR(INDEX($A$3:$A$32,AGGREGATE(15,6,(ROW($A$3:$A$32)-ROW($A$2))/($C$3:$C$32=""),ROWS($A$1:$A1))),"")
    The ascending list of even number addresses is populated using: =IFERROR(INDEX($A$3:$A$32,AGGREGATE(14,6,(ROW($A$3:$A$32)-ROW($A$2))/($C$3:$C$32="Even"),ROWS($A$1:$A1))),"")
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Problem getting conditional formatted dropdown list items to keep their formatting in list
    By scottatbuckman in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-13-2014, 02:39 PM
  2. Using Formatted Numbers
    By inwell in forum Excel General
    Replies: 10
    Last Post: 01-02-2014, 05:56 AM
  3. [SOLVED] Convert all numbers stored as text or custom formatted to numbers &no decimals - 40 sheets
    By synses in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-19-2013, 01:46 AM
  4. some numbers formatted as text
    By peterhw in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-09-2012, 01:59 PM
  5. Incorrectly formatted numbers
    By mikecook in forum Excel General
    Replies: 3
    Last Post: 06-02-2010, 08:37 AM
  6. Numbers formatted as text
    By andrewc in forum Excel General
    Replies: 3
    Last Post: 05-20-2008, 09:02 AM
  7. formatted numbers to CSV problem
    By mark in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-15-2005, 07:15 PM
  8. [SOLVED] Converting numbers formatted as text to numbers
    By Bill in forum Excel General
    Replies: 1
    Last Post: 07-19-2005, 03:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1