Extract set number in different lines in given string

[harinsh]

Hi Team,


is anyone help me with below requirement ...in the given example string ...need to eliminate what is there in brackets and need the numbers in each line. If you see ** thes two stars will separate the each numbers in each string. I believe based on the these stars need to build the macro or any formula....


Example:
123455(final)*6256656(in progress)**7651623(closed)


Output:
123455
6256656
7651623


Let me know if you need any clarification.

 

[Peter_SSs]

Would this user-defined function be any use? To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy across (& down?).
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function GetNum(s As String, Num As Long) As Variant
  Static RX As Object, M As Object

  If RX Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
  End If
  RX.Pattern = "\d+(?=\()"
  Set M = RX.Execute(s)
  GetNum = vbNullString
  If Num <= M.Count Then GetNum = Val(M(Num - 1))
End Function

Excel Workbook
	A	B	C	D	E
1	123455(final)*6256656(in progress)**7651623(closed)	123455	6256656	7651623
Sheet1

 

[harinsh]

Amazing and very helpful ....Thank you for your help ...

 

[Peter_SSs]

Amazing and very helpful ....Thank you for your help ...

You are welcome. Thanks for letting us know.