Search for multiple text strings and return abbreviations if present

[mordecai98]

In column B, the following options are available:
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>Session 1: Sunday July 1st – Friday July 6th, Session 2: Sunday July 8th – Friday July 13th, Session 3: Sunday July 15th – Friday July 20th, Session 4: Sunday July 22nd– Friday July 27th
In column C, if Session 1 is resent, I'd like to display 1. If Session 1 and Session 2 are present, I'd like to display 1,2, etc.

I can do this for just Session 1, but not sure how to do this with all 4 sessions.

Thanks

 

[Special-K99]

In column B, the following options are available:
Session 1: Sunday July 1st – Friday July 6th, Session 2: Sunday July 8th – Friday July 13th, Session 3: Sunday July 15th – Friday July 20th, Session 4: Sunday July 22nd– Friday July 27th

Is this in one cell or several cells in column B? You haven't specified.

How many sessions can there be as a maximum?

 

[mordecai98]

This should all be in one cell.
There can be 4 sessions Max.

 

[mordecai98]

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col width="237"></colgroup><tbody>[TR]
[TD]Session 1: Sunday July 1st – Friday July 6th, Session 2: Sunday July 8th – Friday July 13th, Session 3: Sunday July 15th – Friday July 20th, Session 4: Sunday July 22nd– Friday July 27th[/TD]
[/TR]
[TR]
[TD]Session 1: Sunday July 1st – Friday July 6th, Session 2: Sunday July 8th – Friday July 13th, Session 3: Sunday July 15th – Friday July 20th, Session 4: Sunday July 22nd– Friday July 27th[/TD]
[/TR]
[TR]
[TD]Session 1: Sunday July 1st – Friday July 6th, Session 2: Sunday July 8th – Friday July 13th, Session 3: Sunday July 15th – Friday July 20th, Session 4: Sunday July 22nd– Friday July 27th[/TD]
[/TR]
[TR]
[TD] Session 3: Sunday July 15th – Friday July 20th, Session 4: Sunday July 22nd– Friday July 27th
[/TD]
[/TR]
</tbody>[/TABLE]
This is how they look. The cell to the right should show 1,2,3,4 or 3,4.

 

[Special-K99]

Will this do?
(Not comma separated)

=TRIM(IF(ISNUMBER(SEARCH("Session 1",A1)),"1 ","")&IF(ISNUMBER(SEARCH("Session 2",A1)),"2 ","")&IF(ISNUMBER(SEARCH("Session 3",A1)),"3 ","")&IF(ISNUMBER(SEARCH("Session 4",A1)),"4 ",""))

 

[FormR]

This should all be in one cell.
There can be 4 sessions Max.

Hi, welcome to the forum!

Here is just another option that you can try.

Excel 2013/2016
	A	B
1	Session 1: Sunday July 1st Friday July 6th, Session 2: Sunday July 8th Friday July 13th, Session 3: Sunday July 15th Friday July 20th, Session 4: Sunday July 22nd Friday July 27th	1,2,3,4
2	Session 1: Sunday July 1st Friday July 6th, Session 2: Sunday July 8th Friday July 13th, Session 3: Sunday July 15th Friday July 20th, Session 4: Sunday July 22nd Friday July 27th	1,2,3,4
3	Session 1: Sunday July 1st Friday July 6th, Session 2: Sunday July 8th Friday July 13th, Session 3: Sunday July 15th Friday July 20th, Session 4: Sunday July 22nd Friday July 27th	1,2,3,4
4	Session 3: Sunday July 15th Friday July 20th, Session 4: Sunday July 22nd Friday July 27th	3,4
5	Session 1: Sunday July 15th Friday July 20th, Session 4: Sunday July 22nd Friday July 27th	1,4
6	Session 4: Sunday July 15th Friday July 20th	4
Sheet1

Cell Formulas
Range	Formula
B1	=SUBSTITUTE(TRIM(SUBSTITUTE(SUMPRODUCT(0+ISNUMBER(SEARCH("Session "&{1,2,3,4}&":",A1)),{1000000,20000,300,4}),0," "))," ",",")

 

[mordecai98]

Fantastic! Thank you!!