+ Reply to Thread
Results 1 to 9 of 9

How to separate variable text cells

  1. #1
    Registered User
    Join Date
    09-11-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    5

    How to separate variable text cells

    Hi,

    I have a list of text data (over 2000 rows) which I need to separate based on the month. This is an example;

    Masldkfj: Simbisai 28 Jun 12 MAU/MSSU/MBS
    Mrtyu: Regis 23 Jun 12 MAU/MSSU/MBS
    Mrtuu: Regis 26 Jun 12 Beech 1
    Sisdf: Jasbinder 29 May 12 Avon 3
    Ssfd: Jasbinder 29 May 12 Avon 4
    Ssdf: Jasbinder 31 May 12 Avon (1)ORTHOPA
    Ssdf: Jasbinder 31 May 12 Avon (1)ORTHOPA


    Each cell is of variable length and the month is in a variable position within the cell.

    Can anyone advise the quickest way to do this?

    Thanks in advance!

  2. #2
    Registered User
    Join Date
    08-03-2005
    Location
    Harpers Ferry, WV
    Posts
    26

    Re: How to separate variable text cells

    If you can be sure that there are no numbers prior to the date, you could always search the string for the first number and then use the next 9 elements to get the date.
    Old Programmers Never Die ... They Just Lose Their Bits

  3. #3
    Registered User
    Join Date
    09-11-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: How to separate variable text cells

    There are never any numbers before the date. I just need to separate the May's from the June's (in the above example) so I guess I'd just need the next 6 elements?? Can you help me with the formula to do this too please?!

    Thanks so much!

  4. #4
    Registered User
    Join Date
    08-03-2005
    Location
    Harpers Ferry, WV
    Posts
    26

    Re: How to separate variable text cells

    You need to define what you mean by separate the May's from the June's (or whatever month).

  5. #5
    Registered User
    Join Date
    09-11-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: How to separate variable text cells

    I want to be able to separate any cells that contains "May" into one column and any that contain "June" into another.. At the moment they're all in the same column. So, the spreadsheet looks like this;

    Masldkfj: Simbisai 28 Jun 12 MAU/MSSU/MBS 379.5
    Mrtyu: Regis 23 Jun 12 MAU/MSSU/MBS 231
    Mrtuu: Regis 26 Jun 12 Beech 1 231
    Sisdf: Jasbinder 29 May 12 Avon 3* 239.25
    Ssfd: Jasbinder 29 May 12 Avon 4 158.5
    Ssdf: Jasbinder 31 May 12 Avon (1)ORTHOPA 379.5
    Ssdf: Jasbinder 31 May 12 Avon (1)ORTHOPA 429

    (text column A and the amounts in column B)

    I need to total the amounts relating to May and those relating to June.

    Does that explain it better...?

  6. #6
    Forum Contributor
    Join Date
    09-11-2009
    Location
    Paris
    MS-Off Ver
    Excel 2003
    Posts
    102

    Re: How to separate variable text cells

    salut,
    you can combine two function find and right for getting it...
    if you use Excel 2007+. formula is shorter
    {=IF(COUNTIF(A8,"*"&$G$1&"*")>0,MAX(IFERROR(--RIGHT(A8,ROW(INDIRECT("1:"&LEN(A8)))),0)))}
    A8: your text data
    see attached file with version 2003
    ^o^
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    09-11-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: How to separate variable text cells

    Oh thanks! I'm nearly there - however the numbers in my example above are the next cell to the right of the text cells, not in the same cell. How does the formula change then?

  8. #8
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: How to separate variable text cells

    nztoni,

    Welcome to the forum!
    I think you just need a sumif formula. Attached is an example workbook based on the criteria you described.
    In column A are the text strings
    In column B are the amounts
    In column D are the months (Jan, Feb, Mar, etc...)
    In column E are the total amounts for each month. In order to get that total, in cell E2 and copied down is this formula:
    Please Login or Register  to view this content.
    Is something like that what you're looking for?
    Attached Files Attached Files
    Last edited by tigeravatar; 09-11-2012 at 10:45 AM. Reason: Forgot to add attachment
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  9. #9
    Registered User
    Join Date
    09-11-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: How to separate variable text cells



    That's it! You're a life saver!

    Thank you all very much for your help - what a great forum!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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