• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find number if 8 or 10 Digit from sentence

For the 8 digit number you could use:
=MID(A2,MIN(IF(ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},A2)),FIND({1,2,3,4,5,6,7,8,9,0},A2))),FIND(" ",A2,MIN(IF(ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},A2)),FIND({1,2,3,4,5,6,7,8,9,0},A2))))-MIN(IF(ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},A2)),FIND({1,2,3,4,5,6,7,8,9,0},A2))))

If the 10 digit Number is always on the right you can simply use: =Right(A2,10)

Copy both down
 
Hui Number is not fix place any where in sentence & this formula give number if in sentence but i want if that numbe is 8 digt then i want that number & also 10 digit number always with "-" more than 1 numbers if then all these numbers i want so can u please tell me
 
For 8 Digit if it is in left of 10 digit.

Code: 
=MID(A2,FIND({1,2,3,4,5,6,7,8,9,0},A2),FIND(" ",A2,FIND({1,2,3,4,5,6,7,8,9,0},A2))-FIND({1,2,3,4,5,6,7,8,9,0},A2))

for 10 digit

Code: 
=MID(A2,FIND("-",A2)-8,10)
 
For 8 digit....

=IFERROR(MID(A2,FIND({1,2,3,4,5,6,7,8,9,0},A2,FIND("-",A2)+2),FIND(" ",A2&" ",FIND({1,2,3,4,5,6,7,8,9,0},A2,FIND("-",A2)+2))-FIND({1,2,3,4,5,6,7,8,9,0},A2,FIND("-",A2)+2)),MID(A2,FIND({1,2,3,4,5,6,7,8,9,0},A2),FIND(" ",A2&" ",FIND({1,2,3,4,5,6,7,8,9,0},A2))-FIND({1,2,3,4,5,6,7,8,9,0},A2)))
 
Hi deepak
This Numbers are not fix place any where in sentence i want if 3 numbers then all 3 numbers also if macro please upload the file
 
Abhijeet said:
This work fine but if i have more than 1 number of with "-" then how to find those numbers please tell me
Click to expand...


Check this...
Code: 
Option Explicit

Public Function NumericText(rng As Range, p As Integer)
Dim v() As String, t As String
Dim i As Long, st As String
t = rng.Value
v() = Split(t)
For i = LBound(v) To UBound(v)
  If Not IsNumeric(v(i)) And InStr(v(i), "-") Then st = st & " " & v(i)
Next
NumericText = Split(st)(p)
End Function

use as "=NumericText(A2,1)"
 
Hi deepak this also work fine but i want to use this 2 macro in same workbook please upload the file or tell me how to do this
 
Hi Deepak everything is working fine but i want to ask one more question I have data in excel sentence in between few numbers those sentence have numbers i want that numbers only with different sheet if possible with help of macro. Now manually put the formula in each row so can u tell me macro pull all the numbers from sentence
 
In this macro i have to put manually formulas i want where is numbers in sentence those data in other sheet means all numbers data in 1 sheet
 
You must log in or register to reply here.
Back
Top