Excel Formula

[nianchi111]

Hi,

Is there a way to extract the numbers from the cell.

For example:

A1 <<<<< Manikandan S Natesan (17.09.2019-12:48:31) >>>>> 9161995548 9161995551 9161995552 9161995553 <<<<< Shanmugapriyan Malliraja

B1 I need the numbers starting with 91, which is
9161995548
9161995551
9161995552
9161995553

Currently I use the Mid formula to extract but getting only first 10 digit number. i need all the number in the cell

=Mid(A1,Find(91,A1),10)

Thanks,
Vimal Vikraman.

 

[kweaver]

Does A1 have those other symbols (less than)? Are the numbers always starting with "91"? Are they all 10-digits? Are there at most 4? Could there be fewer or more numbers?
Where do you want all (4) answers? Is VBA an option?

 

[Snakehips]

Similar questions here.
However, if the <<<<< and >>>>> are a common feature of the text and all numbers to extract to same cell, then maybe like....

Excel 2010
	A	B
1	<<<<< Manikandan S Natesan (17.09.2019-12:48:31) >>>>> 9161995548 9161995551 9161995552 9161995553 <<<<< Shanmugapriyan Malliraja	9161995548 9161995551 9161995552 9161995553
Sheet3

Cell Formulas
Range	Formula
B1	=MID(A1,FIND(">>> ",A1)+4,FIND(" <<<",A1)-FIND(">>> ",A1)-4)

Hope that helps.

 

[nianchi111]

Dear kweaver

Yes, those symbols are always there and the number begins with 91 and all are 10 digits. There might be more than 4. Yes if in VBA that would be great !!! Thank you so much for your response !!!

 

[nianchi111]

Similar questions here.
However, if the <<<<< and >>>>> are a common feature of the text and all numbers to extract to same cell, then maybe like....

Excel 2010
	A	B
1	<<<<< Manikandan S Natesan (17.09.2019-12:48:31) >>>>> 9161995548 9161995551 9161995552 9161995553 <<<<< Shanmugapriyan Malliraja	9161995548 9161995551 9161995552 9161995553
Sheet3

Cell Formulas
Range	Formula
B1	=MID(A1,FIND(">>> ",A1)+4,FIND(" <<<",A1)-FIND(">>> ",A1)-4)

Hope that helps.

Thank you so much Tony....

 

[Peter_SSs]

B1 I need the numbers starting with 91, which is
9161995548
9161995551
9161995552
9161995553

Does this mean all those numbers in B1 (like Snakehip's formula gave)?
Or does in mean one number in each cell: B1, B2, B3, ...?

Edit: Ah, I see you have effectively answered this in you last post.

 

[nianchi111]

Does this mean all those numbers in B1 (like Snakehip's formula gave)?
Or does in mean one number in each cell: B1, B2, B3, ...?

Edit: Ah, I see you have effectively answered this in you last post.

Dear Peter,

As you mentioned if that comes in each cell: B1, B2, B3, ...? Would be great.

Here are some examples

<<<<< Jayakumar A (14.09.2019-00:03:20) >>>>> 9162642311 - Need approval <<<<< Jayakumar A (14.09.2019-00:02:59) >>>>> Reason Cod
For this I just need the number 9162642311 in the next cell

<<<<< Nawab Chikkanthar J (26.09.2019-15:52:04) >>>>> 9162234505 - PO has been created against ALOMO vendor, but wo has been rece
For this I just need the number 9162234505 in the next cell

<<<<< Prabhavathy L (09.07.2019-06:36:27) >>>>> PO 9152368598 <<<<< Kannan V (09.07.2019-09:44:30) >>>>> Reason Code: 0 / Descrip
For this I just need the number 9152368598 in the next cell

Thanks,
Vimal Vikraman.

 

[Peter_SSs]

You don't seem to have any other 10 (or more) digit numbers in your strings so you could try one of these user-defined functions. 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 or down as required.

In rows 1-4 I have copied across & in rows below that I have copied down.

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

  If RX Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
    RX.Pattern = "(\D)(91\d{8})(?=\D)"
  End If
  GetNum = vbNullString
  On Error Resume Next
  GetNum = RX.Execute(s)(Num - 1).SubMatches(1)
End Function


Function Get_Num(s As String, Num As Long) As Variant
  Dim i As Long, k As Long
  
  Get_Num = vbNullString
  For i = 1 To Len(s) - 9
    If Mid(s, i, 10) Like "91########" Then
      k = k + 1
      If k = Num Then Get_Num = Mid(s, i, 10)
    End If
  Next i
End Function

Excel Workbook
	A	B	C	D	E	F
1	>>>> 9161995548 9161995551 9161995552 9161995553	9161995548	9161995551	9161995552	9161995553
2	>>>> 9162642311 - Need approval>>>> Reason Cod	9162642311
3	>>>> 9162234505 - PO has been created against ALOMO vendor, but wo has been rece	9162234505
4	>>>> PO 9152368598>>>> Reason Code: 0 / Descrip	9152368598
5
6	>>>> 9161995548 9161995551 9161995552 9161995553	9161995548
7		9161995551
8		9161995552
9		9161995553
10
11
12	> 9161995548 9161995551 9161995552 9161995553	9161995548
13		9161995551
14		9161995552
15		9161995553
16
Sheet1