I am trying to solve an issue in excel in which I have a column of data that contains a mix of both letters and numbers. In a new column I would like to extract if and only if the data column contains a cell that has a 10 digit number that starts with a 9 and if not returns blank.
In the data column there can be numbers greater than 10 digits and they can appear at the beginning, end or not at all in the string. For example, 5389456287521 so while there is a 9 in this data set I would want the output to be blank because the number does not start with 9. There can be a mix of numbers and letters in the data. For example, DGTY 19523874200 which should populate as blank since it does not start with a 9. If for example the data was DGTY 9523874200 it should out with 9523874200.
Thank you
Used a number of different formulas
user28764976 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
2
My suggestion would be
=IFERROR(VALUE(MID(A1,SEARCH("9?????????",A1),10)),"")
However, this formula has a flaw, as it will only consider the first occurrence of 9 in a string. For example, it will work for a1234567s91234567892, but it will fail for a1934567s91234567892.
If you need to handle both of the above cases, you would require a VBA function—at least, that’s the best solution I can think of.
It also does not consider cases where you want to extract only numbers that are exactly ten digits long.
If you only need to handle cases where a cell contains exactly a 10-digit number starting with a 9, the formula mentioned in the comments by Black Cat will work.
Update 1: Based on the comments the following formula might be helpful
=LET(pos,SEARCH(""9????????"",A1),num,LEFT(A1,10),IF(AND(ISNUMBER(num*1),LEFT(A1,1)="9",LEN(num*1)=10),num,""))
This only considers cells that start with a 9 at the beginning. In order to avoid #VALUE just use IFERROR like
=IFERROR(LET(pos,SEARCH(""9????????"",A1),num,LEFT(A1,10),IF(AND(ISNUMBER(num*1),LEFT(A1,1)="9",LEN(num*1)=10),num,"")),"")
Update 2: The following formula would cover the case the 9 is somewhere in the cell
=LET(pos,SEARCH("9????????",A1),num,MID(A1,pos,10),IF(AND(ISNUMBER(num*1),LEN(num*1)=10),num,""))
11
This is not too sophisticated, checks conditions individually by line:
=LET(first,MATCH(TRUE,MID(A1,SEQUENCE(LEN(A1)),1)="9",0),
val,IFERROR(VALUE((MID(A1,first,10))),""),
vala,IFERROR(VALUE(MID(A1,first+10,1)),""),
valb,IFERROR(VALUE(MID(A1,first-1,1)),""),
valc,LEN(TRIM(val))=10,
IF(AND(ISNUMBER(val),NOT(ISNUMBER(vala)),NOT(ISNUMBER(valb)),valc),MID(A1,first,10),""))
3
If you are lucky enough to have regextest, then
=REGEXTEST(A1,"^9d{9}$")
Or if you are looking to extract matches that are preceded by beginning of string or a non-digit,
=IFERROR(REGEXEXTRACT(A2,"(?:^|D)(9d{9})",2),"")
If you want to also check that it’s followed by a non-digit or end of line:
=IFERROR(REGEXEXTRACT(A2,"(?:^|D)(9d{9}(?:$|D))",2),"")
1