Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Covert string to date, etc
Hi guys, just can't figure this out:
1. Suppose I have 100 rows of data, rows are such as: "ABMAR99", "ABMAR02", "ABDEC03", etc. How to convert for example, "ABMAR99" to the first wednesday in March 1999, which is, "1999-03-03"? 2. Suppose other than rows like "ABMAR99", "ABMAR02", "ABDEC03", I also have rows like "AB_MLTE", "ABMAR2C0",etc, and I only want to keep the rows in the format ("ABMAR99", "ABMAR02", "ABDEC03"). In other words: (1) I only want to keep rows with the exact length of 7. (2) For rows with the exact length of 7, I want to delete rows whose third letter is "_". Thanks a lot, appreciate your help! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Covert string to date, etc
Hi,
Suppose, Column A contains that date code, Column B will be the helper column, and Column C will be the answer A2: ABMAR99 at B2, type the following formula: =IF(OR(LEN(A2)7,MID(A2,3,1)="_"),"please delete",DATE(IF(VALUE(RIGHT(A2,2))<=20,2000+RIGHT( A2,2),1900+RIGHT(A2,2)),VLOOKUP(MID(A2,3,3),{"jan" ,1;"feb",2;"mar",3;"apr",4;"may",5;"jun",6;"jul",7 ;"aug",8;"sep",9;"oct",10;"nov",11;"dec",12},2,FAL SE),1)) at C2, type the following formula: =IF(B2="please delete","",TEXT(IF(WEEKDAY(B2,1)<=4,DATE(YEAR(B2), MONTH(B2),5-WEEKDAY(B2,1)),DATE(YEAR(B2),MONTH(B2),12-WEEKDAY(B2,1))),"yyyy-mm-dd")) then sort the helper column, and delete the rows Bobocat "MCI" wrote in message ups.com... Hi guys, just can't figure this out: 1. Suppose I have 100 rows of data, rows are such as: "ABMAR99", "ABMAR02", "ABDEC03", etc. How to convert for example, "ABMAR99" to the first wednesday in March 1999, which is, "1999-03-03"? 2. Suppose other than rows like "ABMAR99", "ABMAR02", "ABDEC03", I also have rows like "AB_MLTE", "ABMAR2C0",etc, and I only want to keep the rows in the format ("ABMAR99", "ABMAR02", "ABDEC03"). In other words: (1) I only want to keep rows with the exact length of 7. (2) For rows with the exact length of 7, I want to delete rows whose third letter is "_". Thanks a lot, appreciate your help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro | Excel Discussion (Misc queries) | |||
Property Let: assign return value of Double when passing String | Excel Discussion (Misc queries) | |||
convert string to date | Excel Worksheet Functions | |||
Convert text string to date | Excel Worksheet Functions | |||
Lookup the month in a date string 01/03/05 | Excel Worksheet Functions |