Extract multiple substrings from within a string

alain2363

New Member
Joined
Feb 13, 2013
Messages
2
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 :
SiteNameDates in 2013
MULMK
05,12,19,26 Jan,

<tbody>
</tbody>
MULPN
21,26,27 Jan,

<tbody>
</tbody>
MULRM
29 Jan,

<tbody>
</tbody>

<tbody>
</tbody>







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:

SiteNameDates in 2013WeekdaysWeekend Days
MULMK05,12,19,26 Jan,4
MULPN21,26,27 Jan,12
MULRM29 Jan,1

<tbody>
</tbody>











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
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
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 :
Site
Name
Dates in 2013
MUL
MK
05,12,19,26 Jan,

<TBODY>
</TBODY>
MUL
PN
21,26,27 Jan,

<TBODY>
</TBODY>
MUL
RM
29 Jan,

<TBODY>
</TBODY>

<TBODY>
</TBODY>







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:

Site
Name
Dates in 2013
Weekdays
Weekend Days
MUL
MK
05,12,19,26 Jan,
4
MUL
PN
21,26,27 Jan,
1
2
MUL
RM
29 Jan,
1

<TBODY>
</TBODY>











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

New Member
Joined
Feb 13, 2013
Messages
2
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

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,918
Messages
5,525,624
Members
409,657
Latest member
19JimRon72

This Week's Hot Topics

Top