triming the cells

[OWAIZ]

HI Champs.

i have a small query... in cell A1 i have this particular content"17s 5m", in cell B1, i need to give the reference of cell A1, but i want the following result.

"17:5" or "175" which will remove all the alphabets and if it possible it will be wonder if can be converted in the time format.
17:51 -- that is 17 minutes & 51 seconds.

thanks
Owaiz

 

[straus]

i think this will take care of the 1st part of your query:

Function ExtractNumber(rCell As Range)
Dim iCount As Integer, i As Integer
Dim sText As String
Dim lNum As String

sText = rCell

For iCount = Len(sText) To 1 Step -1
If IsNumeric(Mid(sText, iCount, 1)) Then
i = i + 1
lNum = Mid(sText, iCount, 1) & lNum
End If

If i = 1 Then lNum = CInt(Mid(lNum, 1, 1))
Next iCount

ExtractNumber = CLng(lNum)
End Function

 

[OWAIZ]

Hi Champ,

Thank u for a quick reply can u pls guide me how to implement that function, where to paste it and how to give the reference..

pls guide.

Regards
owaiz

 

[straus]

Method:
Go to tools> Macro> visual Basic Editor and go Insert>Module and paste in the code. Press Alt+Q and save. The Function will appear under "User Defined" in the Paste Function (Shift+F3).

You will have to got to B2 and put the formula there with reference(RCell) as A2. you can drag the formula down as per your requirement

I think this should solve your problem. Pls revert

 

[OWAIZ]

Hi champ,
thank u for ur quick reply, i tried the method specified by u in the mail, it is working perfectly fine.. no issues,

but, i am getting only the numbers, from the sheet which i wanted, but it's very difficult to segregate the same in hour/mm/ss. is there any way to convert
"17m 3s" to 00:17:03 .... if there is a way.. excel is my best friend..
i am really working hard on this project.. and this is only the hurdle right now..

pls help
regards
owaiz

 

[straus]

if you could send the file to me in excel(email: sdias@hughestele.net) or if you are in Mumbai India, you could give me a call. i'll try to help online (022-7925447)