Extracting a sequence of numbers within a string

NewOrderFac33

NewOrderFac33

Well-known Member
Joined
Sep 26, 2011
Messages
1,275
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
Good afternoon,

I have a series of variable length strings in an Excel list that look a bit like this:

Code: 
promote Trans.no: 1173331 by bobsmith

I want to be able to extract the numeric element of the string which is usually, but not always eight characters

I know however that the number will always be preceded by "Trans.no: " and always followed by " by " as in the above example.

Any suggestions?

As always, thanks in advance

Pete
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
Assuming your text is in A1...

=MID(LEFT(A1,FIND(" by ",A1)-1),FIND("Trans.no: ",A1)+10,99)

The above returns text (which would preserve leading zeroes if there are present), if you want the above as a real number (losing any leading zeroes in the process), then use this...

=0+MID(LEFT(A1,FIND(" by ",A1)-1),FIND("Trans.no: ",A1)+10,99)
 
Upvote 0
Rick,

Can you try your suggestions against the following string:

httr://hlswebdply12p:8080/job/PremiumPump-Baby-Dev/build?token=AccurevCI&delay=300secs&cause=caused by Accurev promote Trans.no: 1173331 by bobhoskins

The first of your suggestions returned a blank, the second #VALUE!

I wonder if it's because there is an earlier instance of "by" in the string ("PremiumPump-Baby")?

#puzzled

Pete
 
Last edited:
Upvote 0
Upvote 0
You had the text " by " occurring before the one after the "Trans.no:" which was screwing up my search. Give this formula a try instead...

=TRIM(MID(SUBSTITUTE(MID(A1,FIND("Trans.no: ",A1)+10,999)," ",REPT(" ",999)),1,999))
 
Upvote 0
Spot on.

You sir, are a gentleman, although I'm at a loss to work out how it knows the position at which to stop returning characters.

Don't suppose you'd care to enlighten me, would you? :)

Pete
 
Upvote 0
Just as an alternative, assuming no more than 15 digits in your number:

=-LOOKUP(2,-LEFT(MID(A1,FIND("Trans.no: ",A1)+10,15),ROW(INDIRECT("1:15"))))
 
Upvote 0
Rory,

Thank you - yes, that works too, although I understand it even less than I did Rick's..! :)

I assume that I adjust the 15s to whatever the maximum length of my numerical sequence is likely to be?

Pete
 
Upvote 0
PeteRooneyHome said:
Spot on.

You sir, are a gentleman, although I'm at a loss to work out how it knows the position at which to stop returning characters.

Don't suppose you'd care to enlighten me, would you? :)
Click to expand...
=TRIM(MID(SUBSTITUTE(MID(A1,FIND("Trans.no: ",A1)+10,999)," ",REPT(" ",999)),1,999))

The red highlighted part of the formula finds the text from the first character (which is 10 characters after the location of the "T" in "Trans.no") to the end of the text. The non-colored part of the formula finds the first field (using a space as the delimiter) in the text given to it (which in this case is the result from the red highlighted text). You may want to check out the generalize method of finding delimited fields within text that I show in my mini-blog article here...

<!-- title / author block -->Get Field from Delimited Text String
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Locked
  • Question
Formula Text String Help - Extract a sequence of numbers/letters from a text string
Replies
1
Views
1K
Fluff
Fluff
J
  • Question
ReExtract a sequence of texts and letters from a text string
Replies
2
Views
1K
Jonny99333
J
C
  • Question
Extracting substring from within a string
Replies
4
Views
675
Sufiyan97
Sufiyan97
E
  • Solved
Extract variable length string between first and last spaces
Replies
6
Views
1K
ExceL0ver
E
JenniferMurphy
  • Question
Recursion example
Replies
0
Views
548
JenniferMurphy
JenniferMurphy

Forum statistics

Threads
1,214,875
Messages
6,122,044
Members
449,063
Latest member
ak94

Share this page

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