# Thread: Automatically constructing text strings

1. ## Automatically constructing text strings

I am a professor and I keep my grades in an Excel worksheet. On one tab, I track attendance. Row 1 has dates, column A has names, and column B is a count of absences. For each student, I place an "X" in a column when they are absent on that date. The count column then counts the absences for including in their grade. For example:

Name_____Count 3/1 3/3 3/8 3/11
Bob________0
Sally______1________X
Ted________2____X____________X

(Sorry for the underlining. I tried to0 format the table with Courier but the forum just stripped out all the extra spaces and there is no table function so I could not figure out any other way to get the columns to line up.)

Is it possible to write a formula to construct a text variable for each student giving the dates of their absences? In the above example, Bob would have "", Sally would have "3/3", and Ted would have "3/1,3/11".

I ask because I upload all this information to an online course management system and it would be nice to show the students the dates I have them down as absence, not just the count.

Ronny

2. Originally Posted by Ronny
........ On one tab, I track attendance. Row 1 has dates,
How is it determined what dates are in Row 1? There were no absences on 3/8.
How many possible dates for Row 1?
Can you attach a sample of the attendance Sheet (tab)?

3. You would need to use code I think - is that OK?

4. If the labesl are in row 1 and the names in Col A, Count in Col B, dates in C-F, in G1 you can enter the formula:
=IF(B2=0,"",LEFT(IF(C2="","",C\$1&", ")&IF(D2="","",D\$1&", ")&IF(E2="","",E\$1&", ")&IF(F2="","",F\$1&", "),LEN(IF(C2="","",C\$1&", ")&IF(D2="","",D\$1&", ")&IF(E2="","",E\$1&", ")&IF(F2="","",F\$1&", "))-2))

And copy it down the column...

Steve

5. Originally Posted by sdckapr
=IF(B2=0,"",LEFT(IF(C2="","",C\$1&", ")&IF(D2="","",D\$1&", ")&IF(E2="","",E\$1&", ")&IF(F2="","",F\$1&", "),LEN(IF(C2="","",C\$1&", ")&IF(D2="","",D\$1&", ")&IF(E2="","",E\$1&", ")&IF(F2="","",F\$1&", "))-2))
It works perfectly, thanks!

Ronny

6. Using above formula, I cannot get it to format with "date" in Column G. I just get raw number [40605]. If I put 40605 in another cell [J2] and format with "date", I get appropriate date, as expected. Why not with this formula return?

7. The formula works just fine, you just have to enter the dates as text, e.g. enter March 4th as '3/4 with a quote mark in front of it.

Ronny

C\$1&

Use:
Text(c\$1, "m/d")&

Replace the "format" with whatever format you want and use this for D1, E1, F1, etc...

The Text function converts the number to a text of a particular format...
Steve

9. This last option seems to be a better choice in that LEN seems to have less effect and more flexibility is possible. An example is the trailing -2 and you can remove the commas in C\$1&", " and D,E,F [in LEN]. Both seem to be unnecessary here. Plus you can enter the dates as normal, as dates not text.
I do not understand why without the LEN portion, it only returns 3.

10. What do you have if there is no Len Portion? You need a len portion the formula does not know how much of a substring to extract.

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•