Extract text of varying length from middle of a text string

[LilT]

I am trying to extract a unique number from a text string, and have tried various combinations of MID and FIND formula etc, but can't get it quite right. Examples of the text is as follows:
20120930-1014-AB
20121231-1014-A
20120930-176-A

The number I need is the middle number between the two dashes (ie 1014 and 176). Note that the first part of the string (20120930) will always be 8 characters long, however the unique number (1014) will vary in length and the last part of the string (AB) will also vary in length.

Any ideas?

Many thanks!

<tbody>
</tbody><colgroup><col></colgroup>

 

[VoG]

Try

=MID(A1,10,FIND("-",SUBSTITUTE(A1,"-","~",1))-FIND("-",A1)-1)+0

 

[Firefly2012]

Hi

One option:

Excel 2007
	A	B
1	20120930-1014-AB	1014
2	20121231-1014-A	1014
3	20120930-176-A	176
Sheet1

Cell Formulas
Range	Formula
B1	=MID(A1,10,FIND("-",A1,10)-10)

 

[LilT]

Thanks, that works perfectly! I knew there must be a simple way of doing this, just couldn't quite get the combinations right.

 

[John Watkins]

I thought I would add my question here as I think this is quite similar. I have text strings like this

Invoice/22/1000/ 10141/1614/PP1 - VO.1 - April 2013/WES <tbody> </tbody><colgroup><col></colgroup>

<tbody>
</tbody>

or

Invoice/25/1000/ 10134/1614/Progress Payment 1 - April

<tbody>
</tbody>

or

I/38/1000/ 72//BringDoublePaymentonInv4285 to/WESTRA The number in first text string (hopefully coloured red) of 10141 is a invoice number. I wish to extract it out to Vlookup against another exported excel report which has a column with invoice number already separated. the second text string's Invoice no is 10134 and the final one is 72. So other than the 3rd text string, the invoice number seems to sit between the 3rd"/" and 4th"/" and be 6 digits. I have been attempting combinations above but can not seem to make it work. PS. If a supplied formula works for all but the 3rd text string that would be still helpful.

Thankyou for any help

<tbody>
</tbody>

 

[John Watkins]

Any ideas guys?

 

[diddi]

I can do in VBA if that is any help...

 

[John Watkins]

I can do in VBA if that is any help...

hi thanks for the response, actually I have never utilized that yet, only ever attempting formulas in cells

 

[Rick Rothstein]

I thought I would add my question here as I think this is quite similar. I have text strings like this

Invoice/22/1000/ 10141/1614/PP1 - VO.1 - April 2013/WES <TBODY> </TBODY>

<TBODY>
</TBODY>

or

Invoice/25/1000/ 10134/1614/Progress Payment 1 - April

<TBODY>
</TBODY>

or

I/38/1000/ 72//BringDoublePaymentonInv4285 to/WESTRA The number in first text string (hopefully coloured red) of 10141 is a invoice number. I wish to extract it out to Vlookup against another exported excel report which has a column with invoice number already separated. the second text string's Invoice no is 10134 and the final one is 72. So other than the 3rd text string, the invoice number seems to sit between the 3rd"/" and 4th"/" and be 6 digits. I have been attempting combinations above but can not seem to make it work. PS. If a supplied formula works for all but the 3rd text string that would be still helpful.

Thankyou for any help

<TBODY>
</TBODY>

Give this formula a try...

=TRIM(MID(SUBSTITUTE(A1,"/",REPT(" ",999)),2998,999))

The above formula returns the number as text (thus preserving leading zeroes), but if you need the number as a real Excel number, then use this instead...

=0+TRIM(MID(SUBSTITUTE(A1,"/",REPT(" ",999)),2998,999))

The general format for this formula (which finds the specified field using a specified delimiter) can be found in my mini-blog article here...

Get Field from Delimited Text String

 

[John Watkins]

Give this formula a try...

=TRIM(MID(SUBSTITUTE(A1,"/",REPT(" ",999)),2998,999))

The above formula returns the number as text (thus preserving leading zeroes), but if you need the number as a real Excel number, then use this instead...

=0+TRIM(MID(SUBSTITUTE(A1,"/",REPT(" ",999)),2998,999))

The general format for this formula (which finds the specified field using a specified delimiter) can be found in my mini-blog article here...

Get Field from Delimited Text String

This works! thankyou the only issue I have now, is the result of either of the above formulas, does not seem to want to be VLooked up by my formula which was previously working. I am getting the #N/A error.