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).
106 Responses
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 🙂
WOW………….!!!! Lovely Bro, Love You 🙂
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
=IF(FIND(“”,B4)=11,LEFT(B4,10),IF(FIND(“”,B4,FIND(“”,B4)+1)-FIND(“”,B4)<10,RIGHT(B4,10),MID(B4,FIND("",B4)+1,10)))
=RIGHT(SUBSTITUTE(B3,CHAR(10),”-“),LEN(SUBSTITUTE(B3,CHAR(10),”-“))-FIND(“-“,SUBSTITUTE(B3,CHAR(10),”-“),FIND(“-“,SUBSTITUTE(B3,CHAR(10),”-“))+1))
Here’s my solution:
=RIGHT(SUBSTITUTE(B3,CHAR(10),”-“),LEN(SUBSTITUTE(B3,CHAR(10),”-“))-FIND(“-“,SUBSTITUTE(B3,CHAR(10),”-“),FIND(“-“,SUBSTITUTE(B3,CHAR(10),”-“))+1))
Nyakno-Abasi Obott
NIGERIA
=IF(FIND(CHAR(10),B3,FIND(CHAR(10),B3,1)+1)-FIND(CHAR(10),B3,1)<10,MID(B3,FIND(CHAR(10),B3,FIND(CHAR(10),B3,1)+1)+1,10),MID(B3,FIND(CHAR(10),B3,1),FIND(CHAR(10),B3,FIND(CHAR(10),B3,1)+1)-FIND(CHAR(10),B3,1)))
My Solution:
=IF(LEN(LEFT(B5,FIND(CHAR(10),B5)-1))=10,LEFT(B5,FIND(CHAR(10),B5)-1),””)&IF(LEN(MID(B5,FIND(CHAR(10),B5)+1,FIND(CHAR(10),B5,FIND(CHAR(10),B5)+1)-FIND(CHAR(10),B5)-1))=10,MID(B5,FIND(CHAR(10),B5)+1,FIND(CHAR(10),B5,FIND(CHAR(10),B5)+1)-FIND(CHAR(10),B5)-1),””)&IF(LEN(RIGHT(B5,LEN(B5)-FIND(CHAR(10),B5,FIND(CHAR(10),B5)+1)))=10,RIGHT(B5,LEN(B5)-FIND(CHAR(10),B5,FIND(CHAR(10),B5)+1)),””)
Nyakno-Abasi Obott
NIGERIA
=IF(FIND(CHAR(10),B3)=11,LEFT(B3,10),IF(FIND(CHAR(10),MID(B3,FIND(CHAR(10),B3)+1,100))=11,MID(B3,FIND(CHAR(10),B3)+1,10),RIGHT(B3,10)))
In excel ceii have number: 94873/777812345ram bahadur.
in another cell lpc777812346
need only started 7778 belong 5 digit.
Like 77781234 and 777812346
=FILTERXML(“
” & SUBSTITUTE(A2,CHAR(10),”“)&”“,”//s[string-length()=10]”)=FILTERXML(“
” & SUBSTITUTE(BF3,CHAR(10),”“)&”“,”//s[string-length()=10]”)Just copy 10 digits number in the next column and press Ctrl+E (Flash fill)
It worked in my computer.
=LET(a,TEXTSPLIT(B3,CHAR(10)),b,LEN(a)=10, FILTER(a,b))
=CONCAT(BYCOL(TEXTSPLIT(B3,CHAR(10)),LAMBDA(a,IF(LEN(a)=10,a,””))))
=SUM((LEN(TEXTSPLIT(B3,CHAR(10)))=10)*(TEXTSPLIT(B3,CHAR(10))))