I need to extract specific numbers from a text string that varies.

For example: I need to extract the second weight from each text i.e 9.
' GW/NW: 10/9 kgs'

Can anyone help please?

As in the number(s) between the second "/" and the " kgs" ?

AndyF wrote:
I need to extract specific numbers from a text string that varies.

For example: I need to extract the second weight from each text i.e 9.
' GW/NW: 10/9 kgs'

Can anyone help please?

Hi,

Not really enough information but this works for your example.

=MID(A1,FIND("/",A1,FIND("/",A1,1)+1)+1,1)

Mike

"AndyF" wrote:

I need to extract specific numbers from a text string that varies.

For example: I need to extract the second weight from each text i.e 9.
' GW/NW: 10/9 kgs'

Can anyone help please?

Hi Andy

One way
=--(MID(A1,FIND("^",SUBSTITUTE(A1,"/","^",2))+1,1))

--
Regards
Roger Govier

"AndyF" wrote in message
...
I need to extract specific numbers from a text string that varies.

For example: I need to extract the second weight from each text i.e 9.
' GW/NW: 10/9 kgs'

Can anyone help please?

Thanks Mike. This formula works for this example, but is there any way I can
extract any number from the text as the spacing and length of digits may
differ.

eg.

(NETT) - 68/58kgs
/NET: 338/320 KG

I only need the figure always after the '/'.

Regards,




"Mike H" wrote:

Hi,

Not really enough information but this works for your example.

=MID(A1,FIND("/",A1,FIND("/",A1,1)+1)+1,1)

Mike

"AndyF" wrote:

I need to extract specific numbers from a text string that varies.

For example: I need to extract the second weight from each text i.e 9.
' GW/NW: 10/9 kgs'

Can anyone help please?

That's correct Bob!

"Bob I" wrote:

As in the number(s) between the second "/" and the " kgs" ?

AndyF wrote:
I need to extract specific numbers from a text string that varies.

For example: I need to extract the second weight from each text i.e 9.
' GW/NW: 10/9 kgs'

Can anyone help please?

On Wed, 13 Aug 2008 09:01:01 -0700, AndyF
wrote:

I need to extract specific numbers from a text string that varies.

For example: I need to extract the second weight from each text i.e 9.
' GW/NW: 10/9 kgs'

Can anyone help please?

It's pretty simple to do using a UDF utilizing Regular Expressions.

To enter the UDF,
<alt-F11 opens the VB Editor
Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens.

To use this, enter the formula:
=RegexSub(A1,".*/(\d+).*","$1")

The relevant pattern is

".*/(\d+).*

which separates out the digit(s) following a "/" from everything else in the
string and captures into "reference 1"

The "$1" means to substitute only what was captured in reference 1 for the
entire string.

The string needs to be all on one single line. If it is on multiple lines, we
need to change the pattern.

============================
Option Explicit

Function RegexSub(Str As String, SrchFor As String, ReplWith As String) As
String
Dim objRegExp As Object

Set objRegExp = CreateObject("vbscript.regexp")
With objRegExp
.Pattern = SrchFor
.IgnoreCase = True
.Global = True
.MultiLine = True
End With

RegexSub = objRegExp.Replace(Str, ReplWith)

End Function
==================================
--ron

Then this would do that
=MID(A1,FIND("/",A1,FIND("/",A1,1)+1)+1,(FIND("
kgs",A1,1)-FIND("/",A1,FIND("/",A1,1)+1)))

Watch the word wrap, mind there is a space before the kgs

AndyF wrote:

That's correct Bob!

"Bob I" wrote:


As in the number(s) between the second "/" and the " kgs" ?

AndyF wrote:

I need to extract specific numbers from a text string that varies.

For example: I need to extract the second weight from each text i.e 9.
' GW/NW: 10/9 kgs'

Can anyone help please?