• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extracting days from numbered list

rm3aq

New Member
Hi guys,


I frequently get a excel sheet with the numbers "1234567" or perhaps "12" in one cell to signify all days of the week in the first case or only Monday and Tuesday in the second case.


My question is that is there a quick way to convert the "1234567" text into days of the week? I believe the TEXT function can change one or two numbers into days but what about such a string?
 
Hi,

You could try something simple like the following:

=CONCATENATE(IF(ISNUMBER(FIND("1",A2 & "")),"Sun",""), IF(ISNUMBER(FIND(2,A2)),"Mon",""),IF(ISNUMBER(FIND(3,A2)),"Tue",""),IF(ISNUMBER(FIND(4,A2)),"Wed",""),IF(ISNUMBER(FIND(5,A2)),"Thu",""),IF(ISNUMBER(FIND(6,A2)),"Fri",""),IF(ISNUMBER(FIND(7,A2)),"Sat",""))


(The above converts the number in A2 to the dayofweek text.)


Some sample results:

[pre]
Code:
345	TueWedThu
12	SunMon
47	WedSat
25	MonThu
257	MonThuSat
142	SunMonWed
[/pre]
Please feel free to add error checking as needed.


Cheers,

Sajan.
 
Sajan's and rm3aq's idea can be merged together:

=CONCATENATE(TEXT(MID(A1,1,1),"ddd"),TEXT(MID(A1,2,1),"ddd"),TEXT(MID(A1,3,1),"ddd"),TEXT(MID(A1,4,1),"ddd"),TEXT(MID(A1,5,1),"ddd"),TEXT(MID(A1,6,1),"ddd"),TEXT(MID(A1,7,1),"ddd"))
 
Back
Top