|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|
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!