Extract Characters from String

MOCCOM

New Member
Joined
Aug 11, 2017
Messages
6
ReferenceDesired OutputComments
250.7537875378Standard reference - without expected suffix i.e. ".000"
250. 7537875378Includes erroneous space
250 .7537875378Includes erroneous space
250.75378.00075378Standard reference (majority of cases)
TextErrorText 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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
How about
=IFERROR(TRIM(LEFT(SUBSTITUTE(TRIM(SUBSTITUTE(A2,LEFT(A2,FIND(".",A2)),"")),".",REPT(" ",100)),100)),"")
 
Upvote 0
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))
 
Upvote 0
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")
 
Upvote 0
With data in staring in A1:

Enter formula in B1
Code:
[B]=IFERROR(TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",50)),50,50))/1,"Error")[/B]
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top