How can I retrieve 6 digits from within my concatenated numbers?-CodePudding

I have a list of numbers which are separated by hyphens. The format and length is always the same. Example:

65-09-27-542400-6147

I want to retrieve the 6 digits from after the third hyphen. Using the data in the above example, the result is:

542400

My formula can only retrieve the numbers from after the first hyphen. Using above example, this will be 09:

=IFERROR(MID(A1,SEARCH("-*-",A1) 1,SEARCH("-",SUBSTITUTE(A1,"-","^",1))-SEARCH("-*-",A1)-1),"")

How can I adjust my current formula to retrieve the 6 digits after the third hyphen instead?

CodePudding user response：

You can also try:

=regexextract(A1,"\d{6}")

CodePudding user response：

I think the easiest and most efficient is to use the MID function if they are all the exact format:

=mid(A1,10,6)

CodePudding user response：

Use SPLIT() function.

=INDEX(TRANSPOSE(SPLIT(A1,"-")),4)