# Extract the 10 digit number [formula homework]

Okay, time for another challenge.

Imagine you have some data like this. Each cell contains 3 numbers separated by line break – CHAR(10) and you need to extract the number that is 10 digits long.

Go ahead and solve this riddle.

**Click here to download sample data workbook.**

Post your answers in comments. Although the title says *formula homework *you are welcome to post VBA, Power Query and RegEx (thru VBA) solutions too.

**Want to extract more?**

If you derive pleasure extracting good data from junk, you are going to love below challenges.

- Extract numbers from text
- Extract first & last names from email addresses
- Extract file name from full path
- Extract dates from text
- Extract hourly rate by looking up a pattern

A note: These challenges are very interesting and can take up a lot of your time (thus make you a whole lot smarter).

### Leave a Reply

Excel Tips, Tricks, Cheats & Hacks – Notable Excel Websites (Non-MVP) Edition |
Apply Conditional Formatting using Slicers |

## 94 Responses to “Extract the 10 digit number [formula homework]”

Power Query Script

let

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

Replaced_Carraige = Table.ReplaceValue(Source,"#(lf)","|",Replacer.ReplaceText,{"Data"}),

Split_Column = Table.SplitColumn(Replaced_Carraige,"Data",Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv),{"Data.1", "Data.2", "Data.3"}),

Final_Column_Added = Table.AddColumn(Split_Column, "Length", each if Text.Length([Data.1]) >= 10 then [Data.1]

else if Text.Length([Data.2]) >= 10 then [Data.2]

else if Text.Length([Data.3]) >= 10 then [Data.3]

else "NA")

in

Final_Column_Added

Step 1 - Replace Value "#(If)" with "|"

Step 2 - Split columns by "|"

Step 3 - Add column with IF function in power query identifying column with 10 digits and taking first column having 10 digits as result.

=RIGHT(SUBSTITUTE(B3,CHAR(10),""),10)*1

Oops, the first formula is wrong as I have make the wrong assumption.

Below is the modified formula to pull the 10 digits whether is the 1st row, 2nd row or the 3rd row.

=IF(FIND(CHAR(10),B3)=11,LEFT(B3,10),IF(FIND(CHAR(10),B3,FIND(CHAR(10),B3)+1)-FIND(CHAR(10),B3)<10,RIGHT(B3,10),MID(B3,FIND(CHAR(10),B3)+1,10)))

Newer version of Power Query can split by Special Characters including Line feeds. See my post from 5/7/2016.

=IF(FIND(CHAR(10);B3)-1=10;LEFT(B3;10);IF(FIND(CHAR(10);B3;FIND(CHAR(10);B3)+1)-FIND(CHAR(10);B3)-1=10;MID(B3;FIND(CHAR(10);B3)+1;10);RIGHT(B3;10)))

Not exactly neat, wouldn't scale, and there's no example with the ten digit number first, but it seems to work 🙂

=IF(FIND(CHAR(10),B3)=10,LEFT(B3,10),IF((FIND(CHAR(10),B3,FIND(CHAR(10),B3)+1)-FIND(CHAR(10),B3))=11,MID(B3,FIND(CHAR(10),B3)+1,10),RIGHT(B3,10)))

@Worm,

I'm sure you meant: =IF(FIND(CHAR(10),B3)=10,LEFT(...

Ooooopsssss,

Just saw the length correction...

If data is in cell A1 then use =IF(FIND(CHAR(10),A1)=11,LEFT(A1,10),IF(FIND(CHAR(10),A1,FIND(CHAR(10),A1)+1)-FIND(CHAR(10),A1)=11,MID(A1:A1,FIND(CHAR(10),A1)+1,10),RIGHT(A1,10)))

Cheers!!

Same answer, but at least you got the first length right, unlike me 🙂

User Defined Function (UDF)

-----------------------------------------------------

Function Extract_10(Str As String)

Arr = Split(Str, Chr(10))

For CL = 0 To UBound(Arr)

If Len(Arr(CL)) = 10 Then Extract_10 = Arr(CL)

Next

End Function

-------------------

In cell C3 type: =Extract_10(B3) and copy down.

=========================

Michael Avidan

ISRAEL

HI Micky,

it is giving me #Name? error

This is genius! And easily generalized to extract a substring of length N (assuming there is only one) set off by a particular non-numeric separator character. Hat's off to you!

Hello Micky,

I like your solution. Was wondering if it can be modified to target any 10 character starting with first 3 characters being one of 20 specific 3-characters i.e. I want any 10-character number but starting with any of the following 802,803,805,806,808,809,810 then 702,703,705...and 902,903,905...910.

Thank you.

@hbillions you can try below UDF:

Function Extract_10(Str As String, rng As Range)

Dim c As Range

Arr = Split(Str, Chr(10))

For CL = 0 To UBound(Arr)

If Len(Arr(CL)) = 10 Then

For Each c In rng

If c.Value "" Then

If Val(Left(Arr(CL), Len(c.Value))) = c.Value Then

Extract_10 = Arr(CL)

Exit Function

End If

End If

Next c

End If

Next

End Function

syntax is

=Extract_10(B3, E1:E10)

here b3 contains value & E1 to E10 contains criterion for which you want to match conditions like 802,803,805,806,808,809,810 then 702,703,705...and 902,903,905...910

Cheers!!

The most complicated one, if value in cell A1:

=LOOKUP(10,LEN(TRIM(MID(SUBSTITUTE(A1,CHAR(10),REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))+1)))=1),99))),TRIM(MID(SUBSTITUTE(A1,CHAR(10),REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))+1)))=1),99)))

Cheers!!

Last one from my side (probably :p), its lesser complex than last one by me

=LOOKUP(10,LEN(TRIM(MID(SUBSTITUTE(CHAR(10)&A1,CHAR(10),REPT(" ",255)),{1,2,3}*255,255))),TRIM(MID(SUBSTITUTE(CHAR(10)&A1,CHAR(10),REPT(" ",255)),{1,2,3}*255,255)))

Cheers!!

@Vaibhav

Check:

1234568790

299004

99004

My bad, i did not check for exception.

This one is updated

=INDEX(TRIM(MID(SUBSTITUTE(CHAR(10)&$A7,CHAR(10),REPT(" ",255)),{1,2,3}*255,255)),1,MATCH(10,LEN(TRIM(MID(SUBSTITUTE(CHAR(10)&A7,CHAR(10),REPT(" ",255)),{1,2,3}*255,255))),0))

Cheers!!

First of all I split the data into three columns with the help of Text To Columns tool. My delimiter was char(10) or Ctrl + J. Then in another column I used formula as

=IF(LEN(D3)=10,D3,IF(LEN(E3)=10,E3,F3))

I like your approach - much easier to debug and to understand months later. Even if the situation called for a single formula solution, this is a good way to get a known good result for validation.

Hi Rudra,

Text to Columns only has Tab, Semicolon, Comma, Space, Other (which supports only one character). How you split the data?

Thanks in advance for sharing..

Hey PJ

Hadn't expected that my comment will get a reply.

Once you clicked on 'Other Character' box, hit Ctrl + J, it forces excel to use char(10) as delimiter. I learned this trick in Chandoo's forum only but don't have the link. Thanks.

I suggest this formula after splitting column =LOOKUP(10,LEN(D2:F2),D2:F2)

Ok, I have the correct answer and it works for 10 digits in all lines;-

=IF((FIND(CHAR(10),B3)-1)=10,LEFT(B3,10),IF(FIND(CHAR(10),B3,(FIND(CHAR(10),B3)+1))-(FIND(CHAR(10),B3)+1)=10,MID(B3,FIND(CHAR(10),B3)+1,10),RIGHT(B3,10)))

Thank you Chandoo for this lovely homework !! I just love this.

Very short, but limited to the sample:

=MID(B3,IF(FIND(CHAR(10),B3,8)=18,8,14),10)

Hello Daniel, your solution gives #VALUE! if then 10 digit number is the first number.

My solution is:

=MID(B3,SEARCH(CHAR(10)&"??????????"&CHAR(10),CHAR(10)&B3&CHAR(10)),10)

This MID-SEARCH approach works with the seeming consistency of the sample values but could fail if the two shorter numbers are ever consecutive with a total of nine digits because the ? wildcard would match the CHAR(10) in between them as a valid tenth character. For example, the following input would give 223923908 as a result.

2239

23908

1518028737

La función de Michael (Micky) Avidan utilizando "the function Split in VBA" es una salida fenomenal...

Yo realicé una mucho más larga "very large"

Aquí esta:

Public Function Extrae10Caracteres(Celda As Range) As Long

If Celda.Count = 1 Then

Dim Texto As String, Caracter10 As String

Dim LargoTexto As Long, ValorCaracter As Long

Dim i As Long

Texto = VBA.CStr(Celda.Value)

LargoTexto = VBA.Len(Texto)

For i = 1 To LargoTexto

ValorCaracter = VBA.Asc(VBA.Mid(Texto, i, 1))

If (ValorCaracter = 10) And (VBA.Len(Caracter10) < 10) Then

Caracter10 = Empty

End If

If (ValorCaracter 10) And (VBA.Len(Caracter10) < 10) Then

Caracter10 = Caracter10 & VBA.Mid(Texto, i, 1)

End If

Next i

Extrae10Caracteres = VBA.CLng(Caracter10)

End If

End Function

----------------------------------------

In Cell C3 type: =Extrae10Caracteres(B3) and copy down.

====================

Regards, Andres Rojas Moncada, Colombia

I'm Sorry, the line code:

If (ValorCaracter 10) And (VBA.Len(Caracter10) < 10) Then

Replace:

If (ValorCaracter"

different operator"10) And (VBA.Len(Caracter10) < 10) Then

Regards

In Power Query assuming the original data is in a table called Table1

let

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

#"Added Index1" = Table.AddIndexColumn(Source, "Index", 1, 1),

#"Changed Type" = Table.TransformColumnTypes(#"Added Index1",{{"Data", type text}, {"Extract", Int64.Type}}),

#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type","Data",Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv),{"Data.1", "Data.2" , "Data.3"}),

#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Data.1", type text}, {"Data.2", type text}}),

#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Extract"}),

#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Index"}, "Attribute", "Value"),

#"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each Text.Length([Value])),

#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 10)),

#"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Attribute", "Custom"}),

#"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Index", "Row"}, {"Value", "Extract"}})

in

#"Renamed Columns"

Good approach. See my 5/7 post for similar solution. I added an Index column before the unpivot, so I could tie the split rows to the original row

How about this

=LOOKUP(10^10,MID(B3,ROW(INDIRECT("1:"&LEN(B3)-9)),10)+0)

This solution is perfect for the consistency of the given samples and gets my vote being both short and creative. However, it won't work if the third number is more than 10 digits. The following example would give 9004123456 as the result.

299004

1760481410

99004123456

=MID(B3,IF(FIND(CHAR(10),B3)=11,1,IF(FIND(CHAR(10),B3,FIND(CHAR(10),B3)+1)-FIND(CHAR(10),B3)=11,FIND(CHAR(10),B3)+1,FIND(CHAR(10),B3,FIND(CHAR(10),B3)+1)+1)),10)

Almost the same ....

ok this seems to be shorter than last one of mine

=LOOKUP(9E+307, --MID(A1,ROW(INDIRECT("1:" & LEN(A1) - 9)), 10))

Cheers!!

blog.ExcelVbaLab.Com

Since this solution follows the same premise as the one from Karthik G, it also gets my vote. However, it too is susceptible to the example I showed in my reply to Karthik's post.

VBA Method (with flexible number length)

Public Function GetNumWithLen(strValue As String, numLength As Integer)

Dim strnum As Variant

Dim i As Integer

strnum = Split(strValue, Chr(10), , vbBinaryCompare)

For i = 0 To 2

If Len(strnum(i)) = numLength Then

GetNumWithLen = strnum(i)

Exit Function

End If

Next

GetNumWithLen = "N/A"

End Function

then have a formula in the cell of

=GetNumWithLen(B3,10)

Here goes Regex solution:

Function ExtNum(cll As String, nDig As Single, cnt As Single) As String

Dim regex As Object

Set regex = CreateObject("VBScript.RegExp")

With regex

.Pattern = "\b\d{" & nDig & "}\b"

.IgnoreCase = True

.Global = True

End With

If regex.Test(cll) Then

Set matches = regex.Execute(cll)

For Each Match In matches

i = i + 1

If i = cnt Then

ExtNum = Match.Value

Exit For

End If

Next Match

Else

ExtNum = ""

End If

Set regex = Nothing

End Function

Cheers!!

blog.ExcelVbaLab.Com

syntax is =ExtNum(A1,10,1)

here A1 is cell with value,

10 for number of matching digits,

1 is to get number of occurrence, i.e. 1st, 2nd, 3rd & so on

Cheers!!

=VALUE(CHOOSE(IF(FIND(CHAR(10),B4)=11,1,IF(FIND(CHAR(10),B4,FIND(CHAR(10),B4)+1)-FIND(CHAR(10),B4)=11,2,3)),LEFT(B4,10),LEFT(RIGHT(B4,LEN(B4)-FIND(CHAR(10),B4)),10),RIGHT(B4,10)))

Scalable, works for 10-digit number in all positions (as long as it's a 3-number set). Lose the VALUE() function if you don't care about a text value being returned.

Can't you just divide the number by 1,000,000 and if the result is greater than 0 and less than 1 display it?

Hi, folks...

my formula :

=IF(FIND(CHAR(10),A1)>10,LEFT(A1,10),IF(CODE(RIGHT(A1,11))=10,RIGHT(A1,10),MID(A1,FIND(CHAR(10),A1)+1,10)))

assuming Each cell contains 3 numbers separated by line break – CHAR(10) and max number is 10-digit

Since all the 10 digit numbers begin with 1 and no others contain 1, I would use =MID(B3,FIND("1",B3),10). Will only work for the sample data given.

Can also use VALUE(MID(B3,FIND(1,B3),10)) if need it to convert to a value

=INDEX(MID(B3,ROW(INDIRECT("1:"&LEN(B3))),10),MATCH(TRUE,LEN(ABS(1*(MID(B3,ROW(INDIRECT("1:"&LEN(B3))),10))))=10,0),1)

Sub Extract10()

Dim RawData As Variant

Dim StrgVal As String

Dim i, j As Integer

For i = 3 To 8

StrgVal = Sheet1.Cells(i, 2).Value

RawData = Split(StrgVal, Chr(10))

For j = 0 To UBound(RawData)

If Len(RawData(j)) = 10 Then

Sheet1.Cells(i, 3).Value = RawData(j)

End If

Next j

Next i

End Sub

1. Select range and click Text to Column

2. Check Others on Step 2 type in ALT+0010 and click finish

3. In the next empty Column type the formula INDEX(A1:C1,MATCH(10,LEN(A1:C1),0)) and CTRL + Enter and fill down

(Assuming data is in column A)

Hi,

Here is my go...!! non array solution..

MID(B3,SEARCH(CHAR(10)&REPT("?",10)&CHAR(10),CHAR(10)&B3&CHAR(10)),10)

This one is cool!!

@Vaibhav,

I still think your formula (with a minor change):

=LOOKUP(9^99,--MID(B3,ROW(INDIRECT("1:"&LEN(B3)-9)),10))

is the coolest (so far...)

Exactly...so do I

But if I were you I would not use Indirect function to generate the sequence of numbers..I would rather use ROW($A$1:INDEX(A:A,len(B3)-9))

even though this will fetch result, but i like to keep a number of functions as less as possible.

@Vaibhav I think yours is cool too, but I prefer Asheesh's because is not volatile.

This is a good solution, and it works for the given samples. But it won't work for others. Daniel H offered an identical solution except for the REPT; see my reply to his post for one such case.

=IF(FIND(CHAR(10),B3)=11,LEFT(B3,10),

IF(FIND(CHAR(10),RIGHT(B3,LEN(B3)-(FIND(",",SUBSTITUTE(B3,CHAR(10),",",1)))))=11,LEFT(RIGHT(B3,LEN(B3)-(FIND(",",SUBSTITUTE(B3,CHAR(10),",",1)))),10),

RIGHT(B3,LEN(B3)-(FIND(CHAR(10),SUBSTITUTE(B3,CHAR(10),",",1),1)))))

so many grey areas & combination of solutions!!

Used GUI to build, then modified code by hand to Merge the final step with and earlier step in the query to avoid having and extra table.

The key was to use a newer Split By feature that let's you split by Special Character of Line Feed. But first add an Index column, so when we unpivot the Split rows, we can tie each Line within a cell to the original Row number. We than add the Length of each line as a Custom Column, filter to only keep the rows with Length 10. Finally, I join it back to original data, and add a test column to match sure I match the expected value.

let

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),

Base = Table.TransformColumnTypes(#"Added Index",{{"Extract", type text}}),

#"Split Column by Delimiter" = Table.SplitColumn(Base,"Data",Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv),{"Data.1", "Data.2", "Data.3"}),

#"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Extract"}),

#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Index", "Data.1", "Data.2", "Data.3"}),

#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", {"Index"}, "Attribute", "Value"),

#"Split Column by Delimiter1" = Table.SplitColumn(#"Unpivoted Other Columns","Attribute",Splitter.SplitTextByDelimiter("Data.", QuoteStyle.Csv),{"Attribute.1", "Attribute.2"}),

#"Removed Columns1" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Attribute.1"}),

#"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Attribute.2", "Column"}}),

#"Inserted Text Length" = Table.AddColumn(#"Renamed Columns", "Length", each Text.Length([Value]), type number),

#"Filtered Rows" = Table.SelectRows(#"Inserted Text Length", each ([Length] = 10)),

#"Removed Columns2" = Table.RemoveColumns(#"Filtered Rows",{"Length"}),

#"Merged Queries" = Table.NestedJoin(#"Removed Columns2",{"Index"},Base,{"Index"},"NewColumn",JoinKind.LeftOuter),

#"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Data", "Extract"}, {"Data", "Extract"}),

#"Removed Columns3" = Table.RemoveColumns(#"Expanded NewColumn",{"Index"}),

#"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns3",{"Data", "Extract", "Value", "Column"}),

#"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns1",{{"Column", "Line"}}),

#"Added Custom" = Table.AddColumn(#"Renamed Columns1", "Match?", each [Extract] =[Value])

in

#"Added Custom"

One more with Power Query

let

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

DupCol = Table.DuplicateColumn(Source, "Data", "DC"),

Split = Table.SplitColumn(DupCol,"DC",Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.None),{"D1", "D2", "D3"}),

AddCol = Table.AddColumn(Split, "Extract", each if Text.Length([D1]) = 10 then [D1] else if Text.Length([D2]) = 10 then [D2] else [D3]),

Final = Table.RemoveColumns(AddCol,{"D1", "D2", "D3"})

in

Final

Here's a generalized solution to search in a string ("Str") for a substring of a particular length ("Length") that is set off from other substrings by a particular character ("Char"). I conceive it as recursive calls of a user-defined function I'll call "ExtractStr" that in the example given we use as follows:

=ExtractStr(ExtractStr(Str,Length,Char),Length,Char)

where

Str = the value in the "Data" column of the example

Length = 10

Char = Char(10)

and the UDF is nested once because there are 3 substrings. If there were n substrings, the UDF would be nested n-2 times.

The UDF is:

Public Function ExtractStr(Str As String, Length As Integer, Char As String) As String

Dim Result As String

Dim Pos As Integer

Pos = InStr(Str, Char)

If Pos < Length + 1 Then

Result = Replace(Str, Left(Str, Pos), "")

Else

Result = Left(Str, Length)

End If

ExtractStr = Result

End Function

Its VERY simple. The formula needed is the following:

=MID(B3,FIND(1,B3,1),10)

@Ernesto,

Although I am not Chandoo's spokesman - he wrote VERY CLEARLY (and I quote): "you need to extract the number that is 10 digits long".

He never mentioned a thing about the first digit of the 10 digits number being "1".

=if(mid(b3,11,1)=char(10), left(b3,10),if(mid(b3,len(b3)-10,1)=char(10),right(b3,10)mid(b3,search(char(10),b3,1)+1,10)))

obviously assumes only 3 sub strings, and assumes there actually is a 10 digit string (ie if there are 3 6 character strings the answer would be incorrect)

Hi everyone,

An array formula (Ctrl+Shift+Enter)

Assuming that Len of col B content always = 23 (case here) and that the 10 digits no cannot be in 1st position (according to the displayed pattern), I built:

{=IF(MAX((MID(B3,ROW(A1:A23),1)=CHAR(10))*ROW(A1:A23))=13,RIGHT(B3,10),MID(B3,FIND(CHAR(10),B3)+1,10))}

Cheers 🙂

Hi again,

Now, the 10 digits no can be in the 1st position !

=IF(FIND(CHAR(10),B3)=11,LEFT(B3,10),IF(MAX((MID(B3,ROW(A1:A23),1)=CHAR(10))*ROW(A1:A23))=13,RIGHT(B3,10),MID(B3,FIND(CHAR(10),B3)+1,10)))

Don't forget C+S+E

Here's my ham-handed solution. Array entered formula. Uses ROW(INDIRECT()) to loop through the string and incrementally extract out 10 characters. Then checks if characters are number, and then returns the maximum (which should be the only) number that is 10 characters long.

{=MAX(IFERROR(1*MID(SUBSTITUTE(B3,CHAR(10),"|"),ROW(INDIRECT("1:"&LEN(B3)-9)), 10), ""))}

@Micah

Not sure why you need SUBSTITUTE(B3,CHAR(10),"|").

=MAX(IFERROR(1*MID($B3,ROW($A$1:INDEX(A:A,LEN($B3)-9)), 10), "")) works for me without SUBSTITUTE.

I like your solution, but it's based on an assumption that there are no numbers there that are lengthier than 10 digits.

I would use Text to Column to get three seperate columns. Then the following formula will extra the number that is 10 characters long.

=IF(LEN(B3)=10,B3,IF(LEN(C3)=10,C3,D3))

=IF(LEN(LEFT(B3,SEARCH(CHAR(10),B3,1)-1))=10,LEFT(B3,SEARCH(CHAR(10),B3,1)-1),IF(LEN(MID(B3,SEARCH(CHAR(10),B3,1)-1+2,FIND(CHAR(10),B3,FIND(CHAR(10),B3)+1)-(SEARCH(CHAR(10),B3,1)-1+2)))=10,MID(B3,SEARCH(CHAR(10),B3,1)-1+2,FIND(CHAR(10),B3,FIND(CHAR(10),B3)+1)-(SEARCH(CHAR(10),B3,1)-1+2)),IF(LEN(RIGHT(B3,LEN(B3)-FIND(CHAR(10),B3,FIND(CHAR(10),B3)+1)))=10,RIGHT(B3,LEN(B3)-FIND(CHAR(10),B3,FIND(CHAR(10),B3)+1)))))

Note : First time have posted some solution on your site. But have taken lot more from your website in my regular work.

Explanation :

Number of Character in a Cell = =LEN(B3)

Finding First Enter Char (10) position = SEARCH(CHAR(10),B3,1)-1

Finding 2nd Enter Char (10) position =FIND(CHAR(10),B3,FIND(CHAR(10),B3)+1)

Finding Left Number =LEFT(B3,SEARCH(CHAR(10),B3,1)-1)

Finding Mid Number =MID(B3,SEARCH(CHAR(10),B3,1)-1+2,FIND(CHAR(10),B3,FIND(CHAR(10),B3)+1)-(SEARCH(CHAR(10),B3,1)-1+2))

Finding Right Number =RIGHT(B3,LEN(B3)-FIND(CHAR(10),B3,FIND(CHAR(10),B3)+1))

Then if Loop to Check the count of Character in left, Mid, Center and where ever the count is 10 show it.

Hope it is the easy solution.

very good explanation.

Thanks

Use with control +shift+ ENter

=MAX(IFERROR(VALUE(MID(B3,ROW(INDIRECT("a1:a"&LEN(B3))),10)),0))

Regards

Nitin Verma

Formula not working when there is number with more than 10 digit.

=MID(CHAR(10)&B3&CHAR(10),SEARCH(CHAR(10)&"??????????"&CHAR(10),CHAR(10)&B3&CHAR(10))+1,10)

Although this solution was my first thought as well, see my replies to Daniel H and Asheesh for an example case where it won't work.

shame on me ... i went the cheap route and used flash fill.

=IF(LEN(LEFT(B3,FIND(CHAR(10),B3)-1))=10,LEFT(B3,FIND(CHAR(10),B3)-1),IF(LEN(RIGHT(B3,LEN(B3)-FIND(CHAR(10),B3,FIND(CHAR(10),B3)+1)))=10,RIGHT(B3,LEN(B3)-FIND(CHAR(10),B3,FIND(CHAR(10),B3)+1)),MID(B3,FIND(CHAR(10),B3)+1,10)))

I like Sam's formula:

-not obscure, directly looks for what was asked

-short

-non array

-works with both digits and alpha characters

-easy to adjust for other length.

I'd suggest slightly shortened version:

=MID(B3,SEARCH(CHAR(10)&REPT("?",10)&CHAR(10),CHAR(10)&B3&CHAR(10)),10)

If we want to extract numbers only then

=IFERROR(--MID(B3,SEARCH(CHAR(10)&REPT("?",10)&CHAR(10),CHAR(10)&B3&CHAR(10)),10),"")

This is a reapplication of the awesome technique Roberto Mensa contributed for the Winning Streak problem Chandoo offered in a previous post. It could be shortened a little with things like --MID(...) instead of VALUE(MID(...)) or $A$1 instead of INDEX(A:A,1), but the core of the solution would remain the same.

Note that it is a CSE array. As far as I can tell it works no matter where the 10 digit number is located and appropriately fails if there is no 10 digit number.

=MID(B3,MATCH(10,FREQUENCY(IF(ISNUMBER(VALUE(MID(B3,ROW(INDEX(A:A,1):INDEX(A:A,LEN(B3))),1))),ROW(INDEX(A:A,1):INDEX(A:A,LEN(B3)))),IF(ISNUMBER(VALUE(MID(B3,ROW(INDEX(A:A,1):INDEX(A:A,LEN(B3))),1))),,ROW(INDEX(A:A,1):INDEX(A:A,LEN(B3))))),0)-10,10)

Not the prettiest of formulas but....

=IF(FIND(CHAR(10),B3)=11,LEFT(B3,10),IF(FIND(CHAR(10),B3,FIND(CHAR(10),B3)+1)-FIND(CHAR(10),B3)=11,RIGHT(LEFT(B3,FIND(CHAR(10),B3,FIND(CHAR(10),B3)+1)),11),RIGHT(B3,10)))

=TEXT(MAX(IFERROR(VALUE(MID(B5,14,10)),0),IFERROR(VALUE(MID(B5,7,10)),0),IFERROR(VALUE(MID(B5,8,10)),0)),"0000000000")

This gave perfect answer.

=MID(A1,MATCH(11,FREQUENCY(ROW($1:$99),ISERR(-MID(A1&-10^9,ROW($1:$99),1))*ROW($1:$99)),)-10,10)

Here's my answer

=IF(LEN(TEXT(A7,"0"))=10,A7,"")

A7 is my initial cell. then just drag down the cell

=INDEX(TRIM(MID(REPLACE(B8,CHAR(10),REPT(" ",100)),{1,100,200},100)), MATCH(10,LEN(TRIM(MID(REPLACE(B8,CHAR(10),REPT(" ",100)),{1,100,200},100))),0))

With CSE:

{=MID(B3,MATCH(10,LEN(REPLACE(MID(B3,ROW(OFFSET($A$1,,,LEN(B3))),10),CHAR(10),)),0),10)}

When this will be answered by Chandoo?

=IF(FIND(CHAR(10),B3)=11,LEFT(B3,10),IF(ISERROR(FIND(CHAR(10),RIGHT(B3,10))),RIGHT(B3,10),MID(B3,FIND(CHAR(10),B3)+1,10)))

IF(LEN(LEFT(B3,FIND(CHAR(10),B3,1)))=10,LEFT(B3,FIND(CHAR(10),B3,1)),IF(LEN(MID(B3,FIND(CHAR(10),B3,1)+1,(FIND(CHAR(10),B3,(FIND(CHAR(10),B3,1))+1))-FIND(CHAR(10),B3,1)-1))=10,MID(B3,FIND(CHAR(10),B3,1)+1,(FIND(CHAR(10),B3,(FIND(CHAR(10),B3,1))+1))-FIND(CHAR(10),B3,1)-1),IF(LEN(MID(B3,(FIND(CHAR(10),B3,FIND(CHAR(10),B3,1)+1))+1,LEN(B3)-(FIND(CHAR(10),B3,FIND(CHAR(10),B3,1)+1))+1))=10,MID(B3,(FIND(CHAR(10),B3,FIND(CHAR(10),B3,1)+1))+1,LEN(B3)-(FIND(CHAR(10),B3,FIND(CHAR(10),B3,1)+1))+1),"No Ten Numbers Exists")))

Dear All,

One Problems

I have one data of date type like this

Date

Feb 15 2016

Oct 17 2016

Jul 15 2016

Jun 16 2016

Apr 30 2017

Mar 19 2017

Jan 10 2017

And i want to get the data in this Format

Desired Output in Date Format

15-Feb-16

17-Oct-16

15-Jul-16

16-Jun-16

30-Apr-17

19-Mar-17

10-Jan-17

So please help on this

@Ashish kumar,

Select the 7 cells range > goto 'DATA' > 'TEXT TO COLUMNS' > mark 'SEPERATED' > NEXT > NEXT > mark date format 'MDY' > 'FINISH'.

-----------------

Michael Avidan

ISRAEL

Sir

By formula

@Ashish kumar,

Try the suggested formula in the lonked picture:

https://postimg.org/image/q549uhuel/

-----------------

Michael Avidan

ISRAEL