triming the cells

OWAIZ

Board Regular
Joined
Jun 20, 2002
Messages
89
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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

straus

Board Regular
Joined
Oct 7, 2002
Messages
192
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

Board Regular
Joined
Jun 20, 2002
Messages
89
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

Board Regular
Joined
Oct 7, 2002
Messages
192
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

Board Regular
Joined
Jun 20, 2002
Messages
89
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

Board Regular
Joined
Oct 7, 2002
Messages
192
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)
 

Forum statistics

Threads
1,144,059
Messages
5,722,272
Members
422,419
Latest member
Havok390

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top