Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Numerical values only
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 cell only 06:15 Pleae help. thanks Saziz -- saziz ------------------------------------------------------------------------ saziz's Profile: http://www.excelforum.com/member.php...fo&userid=6350 View this thread: http://www.excelforum.com/showthread...hreadid=523269 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Numerical values only
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Numerical values only
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 ' ================================================== =========================== -- Kind regards, Niek Otten "saziz" wrote in message ... 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 cell only 06:15 Pleae help. thanks Saziz -- saziz ------------------------------------------------------------------------ saziz's Profile: http://www.excelforum.com/member.php...fo&userid=6350 View this thread: http://www.excelforum.com/showthread...hreadid=523269 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Numerical values only
Thank you Niek Otten and Pete_UK both the suggestions worked perfectly. Saziz -- saziz ------------------------------------------------------------------------ saziz's Profile: http://www.excelforum.com/member.php...fo&userid=6350 View this thread: http://www.excelforum.com/showthread...hreadid=523269 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Numerical values only
Thanks for feeding back.
Pete |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Associated Numerical Values | Excel Discussion (Misc queries) | |||
Return Range of Numerical Values in Single Column based on Frequency Percentage | Excel Worksheet Functions | |||
How can I break values apart that are in the same cell? | Excel Worksheet Functions | |||
Assign numerical values to text codes | Excel Worksheet Functions | |||
How do I stop excel replacing numerical values with the date? | Excel Discussion (Misc queries) |