Excel Formula

nianchi111

Board Regular
Joined
Aug 24, 2007
Messages
197
Office Version
  1. 365
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.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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?
 
Last edited:
Upvote 0
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
AB
1<<<<< Manikandan S Natesan (17.09.2019-12:48:31) >>>>> 9161995548 9161995551 9161995552 9161995553 <<<<< Shanmugapriyan Malliraja9161995548 9161995551 9161995552 9161995553
Sheet3
Cell Formulas
RangeFormula
B1=MID(A1,FIND(">>> ",A1)+4,FIND(" <<<",A1)-FIND(">>> ",A1)-4)


Hope that helps.
 
Upvote 0
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 !!!
 
Last edited:
Upvote 0
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
AB
1<<<<< Manikandan S Natesan (17.09.2019-12:48:31) >>>>> 9161995548 9161995551 9161995552 9161995553 <<<<< Shanmugapriyan Malliraja9161995548 9161995551 9161995552 9161995553
Sheet3
Cell Formulas
RangeFormula
B1=MID(A1,FIND(">>> ",A1)+4,FIND(" <<<",A1)-FIND(">>> ",A1)-4)


Hope that helps.

Thank you so much Tony....
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.

Code:
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
ABCDEF
1>>>> 9161995548 9161995551 9161995552 91619955539161995548916199555191619955529161995553
2>>>> 9162642311 - Need approval>>>> Reason Cod9162642311
3>>>> 9162234505 - PO has been created against ALOMO vendor, but wo has been rece9162234505
4>>>> PO 9152368598>>>> Reason Code: 0 / Descrip9152368598
5
6>>>> 9161995548 9161995551 9161995552 91619955539161995548
79161995551
89161995552
99161995553
10
11
12> 9161995548 9161995551 9161995552 91619955539161995548
139161995551
149161995552
159161995553
16
Sheet1
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

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
Back
Top