Extract multiple substrings from within a string

[alain2363]

I have a tally sheet with field inputs of nurses working days. I need to separate # of week days and of weekend days. The sheet looks like this :
[TABLE="class: grid, width: 50, align: left"]
<tbody>[TR]
[TD]Site[/TD]
[TD]Name[/TD]
[TD]Dates in 2013[/TD]
[/TR]
[TR]
[TD]MUL[/TD]
[TD]MK[/TD]
[TD][TABLE="width: 162"]
<tbody>[TR]
[TD="class: xl66, width: 162"]05,12,19,26 Jan,[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]MUL[/TD]
[TD]PN[/TD]
[TD][TABLE="width: 162"]
<tbody>[TR]
[TD="class: xl66, width: 162"]21,26,27 Jan,[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]MUL[/TD]
[TD]RM[/TD]
[TD][TABLE="width: 162"]
<tbody>[TR]
[TD="class: xl66, width: 162"]29 Jan,[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]







I did some cleaning first: TRIM(SUBSTITUTE(UPPER(C3),"JAN"," "))
I have tried different combinations of
* MID(<source>,FIND(",",<source>+1,2)
* MID(<source>,1,FIND(" ",<source>-1))

And proceed so forth step-by-step, but is there a better way (vba macro?) to get a result of separating the chain of days worked into individual dates and count the number of weekdays and of weekend days:

[TABLE="class: grid, width: 50, align: left"]
<tbody>[TR]
[TD]Site[/TD]
[TD]Name[/TD]
[TD]Dates in 2013[/TD]
[TD]Weekdays[/TD]
[TD]Weekend Days[/TD]
[/TR]
[TR]
[TD]MUL[/TD]
[TD]MK[/TD]
[TD]05,12,19,26 Jan,[/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]MUL[/TD]
[TD]PN[/TD]
[TD]21,26,27 Jan,[/TD]
[TD]1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]MUL[/TD]
[TD]RM[/TD]
[TD]29 Jan,[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]











Thank you for anyone who comes with any idea, even partial ideas and I promise to post my findings for everyone to see. Tallies of this kind are used all over the place.

Alain

 

[Robert Mika]

I have a tally sheet with field inputs of nurses working days. I need to separate # of week days and of weekend days. The sheet looks like this :
[TABLE="class: grid, width: 50, align: left"]
<TBODY>[TR]
[TD]Site
[/TD]
[TD]Name
[/TD]
[TD]Dates in 2013
[/TD]
[/TR]
[TR]
[TD]MUL
[/TD]
[TD]MK
[/TD]
[TD][TABLE="width: 162"]
<TBODY>[TR]
[TD="class: xl66, width: 162"]05,12,19,26 Jan,
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]MUL
[/TD]
[TD]PN
[/TD]
[TD][TABLE="width: 162"]
<TBODY>[TR]
[TD="class: xl66, width: 162"]21,26,27 Jan,
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]MUL
[/TD]
[TD]RM
[/TD]
[TD][TABLE="width: 162"]
<TBODY>[TR]
[TD="class: xl66, width: 162"]29 Jan,
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]







I did some cleaning first: TRIM(SUBSTITUTE(UPPER(C3),"JAN"," "))
I have tried different combinations of
* MID(<SOURCE>,FIND(",",<SOURCE>+1,2)
* MID(<SOURCE>,1,FIND(" ",<SOURCE>-1))

And proceed so forth step-by-step, but is there a better way (vba macro?) to get a result of separating the chain of days worked into individual dates and count the number of weekdays and of weekend days:

[TABLE="class: grid, width: 50, align: left"]
<TBODY>[TR]
[TD]Site
[/TD]
[TD]Name
[/TD]
[TD]Dates in 2013
[/TD]
[TD]Weekdays
[/TD]
[TD]Weekend Days
[/TD]
[/TR]
[TR]
[TD]MUL
[/TD]
[TD]MK
[/TD]
[TD]05,12,19,26 Jan,
[/TD]
[TD][/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]MUL
[/TD]
[TD]PN
[/TD]
[TD]21,26,27 Jan,
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]MUL
[/TD]
[TD]RM
[/TD]
[TD]29 Jan,
[/TD]
[TD]1
[/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]











Thank you for anyone who comes with any idea, even partial ideas and I promise to post my findings for everyone to see. Tallies of this kind are used all over the place.

Alain

Hello and welcome to MrExcel
D2 down
=SUMPRODUCT(--(LEN(F2:M2)>0))-E2
E2 down
=SUM(IF(ISNUMBER(F2:I2),IF(WEEKDAY(F2:I2)={1;7},1)))
Confirm Control+Shift+Enter
F2 down and accross
=IF(COLUMNS($C$2:C2)>LEN($C2)-LEN(SUBSTITUTE($C2,",","")),"",DATEVALUE(TRIM(MID(SUBSTITUTE(","&SUBSTITUTE($C2," ",","),",",REPT(" ",100)),COLUMNS($A$2:A2)*100,100))&SUBSTITUTE(RIGHT($C2,4),",","")&2013))

 

[alain2363]

Hello and welcome to MrExcel
D2 down
=SUMPRODUCT(--(LEN(F2:M2)>0))-E2
E2 down
=SUM(IF(ISNUMBER(F2:I2),IF(WEEKDAY(F2:I2)={1;7},1)))
Confirm Control+Shift+Enter
F2 down and accross
=IF(COLUMNS($C$2:C2)>LEN($C2)-LEN(SUBSTITUTE($C2,",","")),"",DATEVALUE(TRIM(MID(SUBSTITUTE(","&SUBSTITUTE($C2," ",","),",",REPT(" ",100)),COLUMNS($A$2:A2)*100,100))&SUBSTITUTE(RIGHT($C2,4),",","")&2013))

Robert, thank you for your formulaes which I am digesting slowly.
In my second tables of 4 Rows (1,2,3,4) and 5 Columns (A,B,C,D,E),
a) SUMPRODUCT( is for week days
b) SUM(IF(ISNUMBER is for weekend days
c) IF(COLUMNS($C$2:C2 is not being resolved, and i don't understand what it does yet
Could you clarify please.

 

[Robert Mika]

Robert, thank you for your formulaes which I am digesting slowly.
In my second tables of 4 Rows (1,2,3,4) and 5 Columns (A,B,C,D,E),
a) SUMPRODUCT( is for week days
b) SUM(IF(ISNUMBER is for weekend days
c) IF(COLUMNS($C$2:C2 is not being resolved, and i don't understand what it does yet
Could you clarify please.

a) Yes, but this just count of cell with no blank formula blank minus Weekend days
b)yes, this foromula create two ranges- one for Saurday and one for Sunday then SUM then up
c)As we going accross columns we need indication when the TRUE statment(long formula) should stop,
In first example as you go along in will stop at 4th column which is the number of comas in delivered by the LEN(.....formula)