# How to separate variable text cells

1. ## 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?

2. ## 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.

3. ## 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. ## 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. ## 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. ## 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)))}
see attached file with version 2003
^o^

7. ## 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. ## 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?

9. ## 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!

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

#### 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