Extract Characters from String

[MOCCOM]

Reference	Desired Output	Comments
250.75378	75378	Standard reference - without expected suffix i.e. ".000"
250. 75378	75378	Includes erroneous space
250 .75378	75378	Includes erroneous space
250.75378.000	75378	Standard reference (majority of cases)
Text	Error	Text entry should output an Error

<tbody>
</tbody>

I need to extract an order reference that should be embedded in the middle portion of a string i.e. 250.XXXXX.000. The challenges are as follows:
1. The middle portion i.e. XXXXX can vary in length - it is not always 5 digits in length
2. The standard format is not always returned - the majority of fall-out does not include the suffix i.e. ".000"
3. There might be erroneous spaces within the text string - between the characters & periods
4. There might be text instead, which should throw an error

What is the best approach to extract the order number? This is the number sequence that follows the first period AND ends at the second period - IF a second period exists! A formula based approach would be preferred!

Thanks

 

[Fluff]

How about
=IFERROR(TRIM(LEFT(SUBSTITUTE(TRIM(SUBSTITUTE(A2,LEFT(A2,FIND(".",A2)),"")),".",REPT(" ",100)),100)),"")

 

[DanteAmor]

Try

=IFERROR(TRIM(MID(SUBSTITUTE(MID(A2,SEARCH(".",A2)+1,99),".",REPT(" ",50)),1,50)), "Error")

Or

=TRIM(MID(SUBSTITUTE(A2,".",REPT(" ",50)),50,50))

 

[Fluff]

If there is text without any periods in it, it outputs nothing whereas the OP said he wanted an error to be reported.

You're quite right, I missed that bit

=IFERROR(TRIM(LEFT(SUBSTITUTE(TRIM(SUBSTITUTE(A2,LEFT(A2,FIND(".",A2)),"")),".",REPT(" ",100)),100)),"Error")

 

[AlKey]

With data in staring in A1:

Enter formula in B1

[B]=IFERROR(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",50)),50,50))/1,"Error")[/B]