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

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?

Upvotes: 0

Answers (3)

Reputation: 2631

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

=mid(A1,10,6)

Upvotes: 1

Reputation: 12993

You can also try:

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

Upvotes: 2

Reputation: 36945

Use SPLIT() function.

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

Upvotes: 1