Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
len replace function
...........<__LLLL......<__..........<__....... ...<__..
.........LL<__LL........<__..........<__....... ...<__.. ...........<__LL........<__..........<__....... ...<__.. HI all, above is a string with each character representing a date in a month. I would like to be able to repalce each character with a number. For e.g. in the first string the 1st < would be number 11, the 1st L would be number 14 etc.They would also be in separate cells. Any ideas. Thaks again in anticipation. Regards |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
len replace function
Use split and join. Below is an example of the different things you can do. If you input was in a variable called MyStr SplitArray = split(MyStr,"<__") for each str in SplitArray 'convert the string to a date MyDate = DateValue(str) MyYear = Year(MyDate) MyMonth = Month(MyDate) MyDay = Day(MyDate) 'convert date back to string MyNewDate = DateSerial(MyYear,MyMonth,MyDay) 'format date as you like it MyNewDateStr = format(MyNewDate,"MMMM-DD-YYYY") str = MyNewDateStr next str 'put new dates back into a string MyNewStr = Join(MyStr,"<__") -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=176304 Microsoft Office Help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
len replace function
A bit more explanation is required here..
-- Jacob "tom_mcd" wrote: ..........<__LLLL......<__..........<__........ ..<__.. ........LL<__LL........<__..........<__........ ..<__.. ..........<__LL........<__..........<__........ ..<__.. HI all, above is a string with each character representing a date in a month. I would like to be able to repalce each character with a number. For e.g. in the first string the 1st < would be number 11, the 1st L would be number 14 etc.They would also be in separate cells. Any ideas. Thaks again in anticipation. Regards |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
len replace function
sorry for being a bit vague
1st string would be cell A1, 2nd string would be A2 etc. I would like to replace each character in each string with its number in the string. For e.g cell B1 the first . would become 1, cell B2 would become 2 . but I want to keep the letters. so cell B14 would still be L, B16 would still be L but cell B19 would be 19. Is this any clearer. I've got a lot of data from a personnel system that I've got to analyse and it's in the above format. Thanks for having a look "tom_mcd" wrote: ..........<__LLLL......<__..........<__........ ..<__.. ........LL<__LL........<__..........<__........ ..<__.. ..........<__LL........<__..........<__........ ..<__.. HI all, above is a string with each character representing a date in a month. I would like to be able to repalce each character with a number. For e.g. in the first string the 1st < would be number 11, the 1st L would be number 14 etc.They would also be in separate cells. Any ideas. Thaks again in anticipation. Regards |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
len replace function
Just make the LL a unique identifier like ??. then use replace to reple the string Range("A1") = replace(Range(A1"),"??",Range("A1").column) so you could do this Set MyRange = Range("A1:D100") for each cell in Myrange cell = replace(cell,"??",cell.column) next cell -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=176304 Microsoft Office Help |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
len replace function
Personally, I'm still not following what you are trying to do. Is there
anyway you can give an example string with real data and not spaces, dots, underlines, etc.; and then show us how you want that "translated" and where you want that translation to go? -- Rick (MVP - Excel) "tom_mcd" wrote in message ... sorry for being a bit vague 1st string would be cell A1, 2nd string would be A2 etc. I would like to replace each character in each string with its number in the string. For e.g cell B1 the first . would become 1, cell B2 would become 2 . but I want to keep the letters. so cell B14 would still be L, B16 would still be L but cell B19 would be 19. Is this any clearer. I've got a lot of data from a personnel system that I've got to analyse and it's in the above format. Thanks for having a look "tom_mcd" wrote: ..........<__LLLL......<__..........<__........ ..<__.. ........LL<__LL........<__..........<__........ ..<__.. ..........<__LL........<__..........<__........ ..<__.. HI all, above is a string with each character representing a date in a month. I would like to be able to repalce each character with a number. For e.g. in the first string the 1st < would be number 11, the 1st L would be number 14 etc.They would also be in separate cells. Any ideas. Thaks again in anticipation. Regards |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
len replace function
Hi all, these are the strings as imported from a very old oracle database
that we are still using. The example is for February this year. the dots represent 1/2 days, the < weekends and the L = a holiday. because there are no dates and I've to find the dates each individtook a holiday over the last year and there are 800 individauls, i thought the best chance of doing this was to replace exh L with its position in the string and divide by 2 to give the dates. The first string line would show the individual to be on leave on the 8th and 9th of february. Hope this is clearer and sorry for the confusion "Rick Rothstein" wrote: Personally, I'm still not following what you are trying to do. Is there anyway you can give an example string with real data and not spaces, dots, underlines, etc.; and then show us how you want that "translated" and where you want that translation to go? -- Rick (MVP - Excel) "tom_mcd" wrote in message ... sorry for being a bit vague 1st string would be cell A1, 2nd string would be A2 etc. I would like to replace each character in each string with its number in the string. For e.g cell B1 the first . would become 1, cell B2 would become 2 . but I want to keep the letters. so cell B14 would still be L, B16 would still be L but cell B19 would be 19. Is this any clearer. I've got a lot of data from a personnel system that I've got to analyse and it's in the above format. Thanks for having a look "tom_mcd" wrote: ..........<__LLLL......<__..........<__........ ..<__.. ........LL<__LL........<__..........<__........ ..<__.. ..........<__LL........<__..........<__........ ..<__.. HI all, above is a string with each character representing a date in a month. I would like to be able to repalce each character with a number. For e.g. in the first string the 1st < would be number 11, the 1st L would be number 14 etc.They would also be in separate cells. Any ideas. Thaks again in anticipation. Regards . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
len replace function
Try this formula in cell B1:
=IF(ISLETTER(MID(A$1,ROW(),1)),MID(A$1,ROW(),1),RO UNDUP(ROW()/2,0)) Copy down until cell B58 (as the length of the data you have in cell A1 is 58 characters) This will results in a column of dates, two rows for each date as it seems you work with "half days". For the given example the dates will be replaced by the L's on rows 15,16,17, and 18 instead of the 8,8,9,9 that would be there if there was no vacation these days. Is that what you want? Hope this helps / Lars-Åke On Fri, 5 Feb 2010 09:05:01 -0800, tom_mcd wrote: Hi all, these are the strings as imported from a very old oracle database that we are still using. The example is for February this year. the dots represent 1/2 days, the < weekends and the L = a holiday. because there are no dates and I've to find the dates each individtook a holiday over the last year and there are 800 individauls, i thought the best chance of doing this was to replace exh L with its position in the string and divide by 2 to give the dates. The first string line would show the individual to be on leave on the 8th and 9th of february. Hope this is clearer and sorry for the confusion "Rick Rothstein" wrote: Personally, I'm still not following what you are trying to do. Is there anyway you can give an example string with real data and not spaces, dots, underlines, etc.; and then show us how you want that "translated" and where you want that translation to go? -- Rick (MVP - Excel) "tom_mcd" wrote in message ... sorry for being a bit vague 1st string would be cell A1, 2nd string would be A2 etc. I would like to replace each character in each string with its number in the string. For e.g cell B1 the first . would become 1, cell B2 would become 2 . but I want to keep the letters. so cell B14 would still be L, B16 would still be L but cell B19 would be 19. Is this any clearer. I've got a lot of data from a personnel system that I've got to analyse and it's in the above format. Thanks for having a look "tom_mcd" wrote: ..........<__LLLL......<__..........<__........ ..<__.. ........LL<__LL........<__..........<__........ ..<__.. ..........<__LL........<__..........<__........ ..<__.. HI all, above is a string with each character representing a date in a month. I would like to be able to repalce each character with a number. For e.g. in the first string the 1st < would be number 11, the 1st L would be number 14 etc.They would also be in separate cells. Any ideas. Thaks again in anticipation. Regards . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
len replace function
Try this formula in cell B1:
=IF(ISLETTER(MID(A$1,ROW(),1)),MID(A$1,ROW(),1),RO UNDUP(ROW()/2,0)) Copy down until cell B58 (as the length of the data you have in cell A1 is 58 characters) This will results in a column of dates, two rows for each date as it seems you work with "half days". For the given example the dates will be replaced by the L's on rows 15,16,17, and 18 instead of the 8,8,9,9 that would be there if there was no vacation these days. Is that what you want? Hope this helps / Lars-Åke On Fri, 5 Feb 2010 09:05:01 -0800, tom_mcd wrote: Hi all, these are the strings as imported from a very old oracle database that we are still using. The example is for February this year. the dots represent 1/2 days, the < weekends and the L = a holiday. because there are no dates and I've to find the dates each individtook a holiday over the last year and there are 800 individauls, i thought the best chance of doing this was to replace exh L with its position in the string and divide by 2 to give the dates. The first string line would show the individual to be on leave on the 8th and 9th of february. Hope this is clearer and sorry for the confusion "Rick Rothstein" wrote: Personally, I'm still not following what you are trying to do. Is there anyway you can give an example string with real data and not spaces, dots, underlines, etc.; and then show us how you want that "translated" and where you want that translation to go? -- Rick (MVP - Excel) "tom_mcd" wrote in message ... sorry for being a bit vague 1st string would be cell A1, 2nd string would be A2 etc. I would like to replace each character in each string with its number in the string. For e.g cell B1 the first . would become 1, cell B2 would become 2 . but I want to keep the letters. so cell B14 would still be L, B16 would still be L but cell B19 would be 19. Is this any clearer. I've got a lot of data from a personnel system that I've got to analyse and it's in the above format. Thanks for having a look "tom_mcd" wrote: ..........<__LLLL......<__..........<__........ ..<__.. ........LL<__LL........<__..........<__........ ..<__.. ..........<__LL........<__..........<__........ ..<__.. HI all, above is a string with each character representing a date in a month. I would like to be able to repalce each character with a number. For e.g. in the first string the 1st < would be number 11, the 1st L would be number 14 etc.They would also be in separate cells. Any ideas. Thaks again in anticipation. Regards . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
len replace function
Since you posted in the programming newsgroup, I'm assuming a macro solution
is acceptable. Since you are really after only the dates of the "holidays", this macro will list only those holiday dates for you (as opposed to the combination of non-holiday dates and L's that you originally asked for). Also, since your data is set up in half-day increments, I made the assumption that your employees could take a half-day holiday, so the code will report that if it exists (by specifying "am" or "pm", as appropriate, along with the date number). Give this macro a try and see if you can use it instead of what you originally asked for... Sub GetHolidayDates() Dim X As Long, Z As Long Dim StartRow As Long, LastRow As Long, Col As Long Dim CodedDate As String StartRow = 1 LastRow = Cells(Rows.Count, "A").End(xlUp).Row For X = StartRow To LastRow Col = 2 CodedDate = Replace(Cells(X, "A").Value, "<__", "....") For Z = 1 To Len(CodedDate) If Mid(CodedDate, Z, 1) = "L" Then If Z And 1 Then If Mid(CodedDate, Z + 1, 1) = "L" Then Cells(X, Col).Value = (Z + 1) / 2 Else Cells(X, Col).Value = ((Z + 1) / 2) & "(am)" End If Col = Col + 1 ElseIf Mid(CodedDate, Z - 1, 1) < "L" Then Cells(X, Col).Value = (Z / 2) & "(pm)" Col = Col + 1 End If End If Next Next End Sub -- Rick (MVP - Excel) "tom_mcd" wrote in message ... Hi all, these are the strings as imported from a very old oracle database that we are still using. The example is for February this year. the dots represent 1/2 days, the < weekends and the L = a holiday. because there are no dates and I've to find the dates each individtook a holiday over the last year and there are 800 individauls, i thought the best chance of doing this was to replace exh L with its position in the string and divide by 2 to give the dates. The first string line would show the individual to be on leave on the 8th and 9th of february. Hope this is clearer and sorry for the confusion "Rick Rothstein" wrote: Personally, I'm still not following what you are trying to do. Is there anyway you can give an example string with real data and not spaces, dots, underlines, etc.; and then show us how you want that "translated" and where you want that translation to go? -- Rick (MVP - Excel) "tom_mcd" wrote in message ... sorry for being a bit vague 1st string would be cell A1, 2nd string would be A2 etc. I would like to replace each character in each string with its number in the string. For e.g cell B1 the first . would become 1, cell B2 would become 2 . but I want to keep the letters. so cell B14 would still be L, B16 would still be L but cell B19 would be 19. Is this any clearer. I've got a lot of data from a personnel system that I've got to analyse and it's in the above format. Thanks for having a look "tom_mcd" wrote: ..........<__LLLL......<__..........<__........ ..<__.. ........LL<__LL........<__..........<__........ ..<__.. ..........<__LL........<__..........<__........ ..<__.. HI all, above is a string with each character representing a date in a month. I would like to be able to repalce each character with a number. For e.g. in the first string the 1st < would be number 11, the 1st L would be number 14 etc.They would also be in separate cells. Any ideas. Thaks again in anticipation. Regards . |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
len replace function
Thank you very much, your solution is absolutely brilliant. Just what we
needeed. "Rick Rothstein" wrote: Since you posted in the programming newsgroup, I'm assuming a macro solution is acceptable. Since you are really after only the dates of the "holidays", this macro will list only those holiday dates for you (as opposed to the combination of non-holiday dates and L's that you originally asked for). Also, since your data is set up in half-day increments, I made the assumption that your employees could take a half-day holiday, so the code will report that if it exists (by specifying "am" or "pm", as appropriate, along with the date number). Give this macro a try and see if you can use it instead of what you originally asked for... Sub GetHolidayDates() Dim X As Long, Z As Long Dim StartRow As Long, LastRow As Long, Col As Long Dim CodedDate As String StartRow = 1 LastRow = Cells(Rows.Count, "A").End(xlUp).Row For X = StartRow To LastRow Col = 2 CodedDate = Replace(Cells(X, "A").Value, "<__", "....") For Z = 1 To Len(CodedDate) If Mid(CodedDate, Z, 1) = "L" Then If Z And 1 Then If Mid(CodedDate, Z + 1, 1) = "L" Then Cells(X, Col).Value = (Z + 1) / 2 Else Cells(X, Col).Value = ((Z + 1) / 2) & "(am)" End If Col = Col + 1 ElseIf Mid(CodedDate, Z - 1, 1) < "L" Then Cells(X, Col).Value = (Z / 2) & "(pm)" Col = Col + 1 End If End If Next Next End Sub -- Rick (MVP - Excel) "tom_mcd" wrote in message ... Hi all, these are the strings as imported from a very old oracle database that we are still using. The example is for February this year. the dots represent 1/2 days, the < weekends and the L = a holiday. because there are no dates and I've to find the dates each individtook a holiday over the last year and there are 800 individauls, i thought the best chance of doing this was to replace exh L with its position in the string and divide by 2 to give the dates. The first string line would show the individual to be on leave on the 8th and 9th of february. Hope this is clearer and sorry for the confusion "Rick Rothstein" wrote: Personally, I'm still not following what you are trying to do. Is there anyway you can give an example string with real data and not spaces, dots, underlines, etc.; and then show us how you want that "translated" and where you want that translation to go? -- Rick (MVP - Excel) "tom_mcd" wrote in message ... sorry for being a bit vague 1st string would be cell A1, 2nd string would be A2 etc. I would like to replace each character in each string with its number in the string. For e.g cell B1 the first . would become 1, cell B2 would become 2 . but I want to keep the letters. so cell B14 would still be L, B16 would still be L but cell B19 would be 19. Is this any clearer. I've got a lot of data from a personnel system that I've got to analyse and it's in the above format. Thanks for having a look "tom_mcd" wrote: ..........<__LLLL......<__..........<__........ ..<__.. ........LL<__LL........<__..........<__........ ..<__.. ..........<__LL........<__..........<__........ ..<__.. HI all, above is a string with each character representing a date in a month. I would like to be able to repalce each character with a number. For e.g. in the first string the 1st < would be number 11, the 1st L would be number 14 etc.They would also be in separate cells. Any ideas. Thaks again in anticipation. Regards . . |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
len replace function
You are quite welcome. Just for my own curiosity... was my guess about
needing the am/pm indicator for half holidays correct? -- Rick (MVP - Excel) "tom_mcd" wrote in message ... Thank you very much, your solution is absolutely brilliant. Just what we needeed. "Rick Rothstein" wrote: Since you posted in the programming newsgroup, I'm assuming a macro solution is acceptable. Since you are really after only the dates of the "holidays", this macro will list only those holiday dates for you (as opposed to the combination of non-holiday dates and L's that you originally asked for). Also, since your data is set up in half-day increments, I made the assumption that your employees could take a half-day holiday, so the code will report that if it exists (by specifying "am" or "pm", as appropriate, along with the date number). Give this macro a try and see if you can use it instead of what you originally asked for... Sub GetHolidayDates() Dim X As Long, Z As Long Dim StartRow As Long, LastRow As Long, Col As Long Dim CodedDate As String StartRow = 1 LastRow = Cells(Rows.Count, "A").End(xlUp).Row For X = StartRow To LastRow Col = 2 CodedDate = Replace(Cells(X, "A").Value, "<__", "....") For Z = 1 To Len(CodedDate) If Mid(CodedDate, Z, 1) = "L" Then If Z And 1 Then If Mid(CodedDate, Z + 1, 1) = "L" Then Cells(X, Col).Value = (Z + 1) / 2 Else Cells(X, Col).Value = ((Z + 1) / 2) & "(am)" End If Col = Col + 1 ElseIf Mid(CodedDate, Z - 1, 1) < "L" Then Cells(X, Col).Value = (Z / 2) & "(pm)" Col = Col + 1 End If End If Next Next End Sub -- Rick (MVP - Excel) "tom_mcd" wrote in message ... Hi all, these are the strings as imported from a very old oracle database that we are still using. The example is for February this year. the dots represent 1/2 days, the < weekends and the L = a holiday. because there are no dates and I've to find the dates each individtook a holiday over the last year and there are 800 individauls, i thought the best chance of doing this was to replace exh L with its position in the string and divide by 2 to give the dates. The first string line would show the individual to be on leave on the 8th and 9th of february. Hope this is clearer and sorry for the confusion "Rick Rothstein" wrote: Personally, I'm still not following what you are trying to do. Is there anyway you can give an example string with real data and not spaces, dots, underlines, etc.; and then show us how you want that "translated" and where you want that translation to go? -- Rick (MVP - Excel) "tom_mcd" wrote in message ... sorry for being a bit vague 1st string would be cell A1, 2nd string would be A2 etc. I would like to replace each character in each string with its number in the string. For e.g cell B1 the first . would become 1, cell B2 would become 2 . but I want to keep the letters. so cell B14 would still be L, B16 would still be L but cell B19 would be 19. Is this any clearer. I've got a lot of data from a personnel system that I've got to analyse and it's in the above format. Thanks for having a look "tom_mcd" wrote: ..........<__LLLL......<__..........<__........ ..<__.. ........LL<__LL........<__..........<__........ ..<__.. ..........<__LL........<__..........<__........ ..<__.. HI all, above is a string with each character representing a date in a month. I would like to be able to repalce each character with a number. For e.g. in the first string the 1st < would be number 11, the 1st L would be number 14 etc.They would also be in separate cells. Any ideas. Thaks again in anticipation. Regards . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can you have a value created by a function replace the function? | Excel Worksheet Functions | |||
Replace function | Excel Discussion (Misc queries) | |||
REPLACE Function | Excel Worksheet Functions | |||
How i can replace function | Excel Worksheet Functions | |||
Replace XLA with XLL function | Excel Programming |