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

.


.