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
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,540
Office Version
  1. 365
Platform
  1. Windows
How about
=IFERROR(TRIM(LEFT(SUBSTITUTE(TRIM(SUBSTITUTE(A2,LEFT(A2,FIND(".",A2)),"")),".",REPT(" ",100)),100)),"")
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,540
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Oct 15, 2013
Messages
395
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:

Watch MrExcel Video

Forum statistics

Threads
1,109,383
Messages
5,528,377
Members
409,817
Latest member
JiNXX9500

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top