Numerical values only

S

saziz

Hi,
How can I transfer from one cell to other only numerical values.
Like in one cell I have "sun rise time 06:15" I want in adjecent cel
only 06:15
Pleae help.
thanks
Sazi
 
P

Pete_UK

If your data is always like that, i.e. time information occupying the
last 5 characters of the string, then you can do this, assuming the
data is in cell A1:

=VALUE(RIGHT(A1,5)&":00")

and format the cell with the formula in as hh:mm.

Hope this helps.

Pete
 
N

Niek Otten

You could use this User defined Function.

If you're new to VBA, read this first:

http://www.mvps.org/dmcritchie/excel/getstarted.htm


The function:

' =============================================================================

Function StripTxt(a As String) As String

' Strips all non-numeric characters from a string, but leaves colons

' Returns a string, not a number!

Dim i As Long

Dim b As String

For i = 1 To Len(a)

b = Mid$(a, i, 1)

If ((Asc(b) > 47 And Asc(b) < 58) Or b = ":") Then StripTxt = StripTxt + b

Next i

End Function

' =============================================================================
 
Top