extracting data from a string a characters

[jkjensen4144]

I am trying to extract some data from a string of characters. In this example "Invoice: 07022018: 28680: 07/02/2018" I want to extract the 28680 that is between the second an third colons. How do I do that?

 

[Fluff]

Hi & welcome to the board.
Will it always be the between the 2nd & 3rd colons?

 

[Rick Rothstein]

In this example "Invoice: 07022018: 28680: 07/02/2018" I want to extract the 28680 that is between the second an third colons. How do I do that?

You use of the words "in this example" makes it sound like you will not always be looking for the value between the second and third colons, hence Fluff's question. However, if you will always want what is between the second and third colons, then you can use this formula...

=TRIM(MID(SUBSTITUTE(A1,":",REPT(" ",100)),200,100))

Note: The formula assume the text in cell A1 will never be longer than 100 characters.