On Feb 27, 12:59*pm, "Tim Williams" <timjwilliams at gmail dot com
wrote:
If len(cell.value)<6 then
* * gethour=""
* * exit function
else
* * v=trim(replace(cell.value,Chr(160),""))
* * t=Split(v," ")(1)
* * gethour=Split(t,":")(0)
end if

Assuming all of your values are either (almost) empty or comform to the
pattern you showed.

BTW it's more convenient to use the VBA
* Trim()
* Replace()
* Instr()
directly instead of their worksheetfunction siblings.

Tim

"Raj" wrote in message

...
Hi
I have a column containing values that look like dates and times but
are not Excel dates:
eg:
4/27/2007 21:15 P.M
4/27/2007 9 P.M
(Some of the cells in the column are blank or have a "-" in them)

I need to extract the hour from this viz "21" and "9" in the examples
above . I wrote the following UDF. It works fine, except that #VALUE!
appears where the column does not have a date. How do I fix this?

Function gethour1(cell As Range) As String
Dim rspstring As String
Dim rspspace As Integer
Dim gethour As String
rspstring =
Application.WorksheetFunction.Trim(Application.Wor ksheetFunction.Substitute*(cell,
Chr(160), ""))
rspspace = Application.WorksheetFunction.Find(" ", rspstring)
If IsNumeric(Mid(rspstring, rspspace + 2, 1)) Then gethour =
Mid(rspstring, rspspace + 1, 2) Else gethour = Mid(rspstring, rspspace
+ 1, 1)
On Error GoTo Errvalue:
gethour1 = CInt(gethour)
Exit Function
Errvalue:
gethour1 = "Not a date"
End Function

Thanks in advance for all the help.

Regards,
Raj

Thanks Tim, It worked.

Raj