Hi everybody,

I have the following in Cell B2 :-

LD(24,6,3,6)=163

How can I extract with VBA the ...

24 < this could be 3 digits
6 < this could be 2 digits
3 < this could be 2 digits
6 < this could be 2 digits
163 < this could be 4 OR 5 digits

.... so they go in Cells E6:E10 please. There will always be a comma
and the final figure will always follow the = sign.

Thanks in Advance.
All the Best.
Paul

I love string manipulation

Sub test()

OldString = "LD(24, 6, 3, 6) = 163"

NewString = Mid(OldString, 4) 'Remove LD(
Var1 = Val(Left(NewString, InStr(NewString, ",") - 1))
NewString = Mid(NewString, InStr(NewString, ",") + 1)
Var2 = Val(Left(NewString, InStr(NewString, ",") - 1))
NewString = Mid(NewString, InStr(NewString, ",") + 1)
Var3 = Val(Left(NewString, InStr(NewString, ",") - 1))
NewString = Mid(NewString, InStr(NewString, ",") + 1)
Var4 = Val(Left(NewString, InStr(NewString, ")") - 1))
Var5 = Val(Mid(NewString, InStr(NewString, "=") + 1))


End Sub

"Paul Black" wrote:

Hi everybody,

I have the following in Cell B2 :-

LD(24,6,3,6)=163

How can I extract with VBA the ...

24 < this could be 3 digits
6 < this could be 2 digits
3 < this could be 2 digits
6 < this could be 2 digits
163 < this could be 4 OR 5 digits

.... so they go in Cells E6:E10 please. There will always be a comma
and the final figure will always follow the = sign.

Thanks in Advance.
All the Best.
Paul

On Mon, 27 Aug 2007 09:50:58 -0700, Paul Black
wrote:

Hi everybody,

I have the following in Cell B2 :-

LD(24,6,3,6)=163

How can I extract with VBA the ...

24 < this could be 3 digits
6 < this could be 2 digits
3 < this could be 2 digits
6 < this could be 2 digits
163 < this could be 4 OR 5 digits

... so they go in Cells E6:E10 please. There will always be a comma
and the final figure will always follow the = sign.

Thanks in Advance.
All the Best.
Paul

One simple way is to write a short UDF allowing you to use a Regular Expression
to extract the values. You can then use this formula:

=ReExtr(cell_ref,regex,Index)

Where index is, in your example between 1 and 5 to extract the 1st, 2nd, etc
group of digits.

The regex to pick out a series of digits is "\d+".

So, with data in A1, you could enter

B1: =ReExtr($A$1,"\d+",COLUMNS($A:A))

and fill right to F1.

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:

===========================
Option Explicit
Function ReExtr(str As String, sPattern As String, Index As Long)
Dim re As Object
Dim mc As Object

Set re = CreateObject("vbscript.regexp")
re.Pattern = sPattern
re.Global = True

Set mc = re.Execute(str)

If Index <= mc.Count Then
ReExtr = mc(Index - 1)
Else
ReExtr = ""
End If
End Function
====================================

--ron

Sub blaH6()
r = Split(Range("B2").Value, ",")
p = Split(r(3), ")=")
q = Split(r(0), "(")
Range("E6") = q(1)
Range("E7") = r(1)
Range("E8") = r(2)
Range("E9") = p(0)
Range("E10") = p(1)
'Range("E9:E10") = Application.WorksheetFunction.Transpose(p) 'replaces
'previous 2 lines if you want
End Sub

--
p45cal


"Ron Rosenfeld" wrote:

On Mon, 27 Aug 2007 09:50:58 -0700, Paul Black
wrote:

Hi everybody,

I have the following in Cell B2 :-

LD(24,6,3,6)=163

How can I extract with VBA the ...

24 < this could be 3 digits
6 < this could be 2 digits
3 < this could be 2 digits
6 < this could be 2 digits
163 < this could be 4 OR 5 digits

... so they go in Cells E6:E10 please. There will always be a comma
and the final figure will always follow the = sign.

Thanks in Advance.
All the Best.
Paul

One simple way is to write a short UDF allowing you to use a Regular Expression
to extract the values. You can then use this formula:

=ReExtr(cell_ref,regex,Index)

Where index is, in your example between 1 and 5 to extract the 1st, 2nd, etc
group of digits.

The regex to pick out a series of digits is "\d+".

So, with data in A1, you could enter

B1: =ReExtr($A$1,"\d+",COLUMNS($A:A))

and fill right to F1.

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:

===========================
Option Explicit
Function ReExtr(str As String, sPattern As String, Index As Long)
Dim re As Object
Dim mc As Object

Set re = CreateObject("vbscript.regexp")
re.Pattern = sPattern
re.Global = True

Set mc = re.Execute(str)

If Index <= mc.Count Then
ReExtr = mc(Index - 1)
Else
ReExtr = ""
End If
End Function
====================================

--ron

Thanks for the replies,

Is there any way using VBA code that I can pick up the string in Cell
B2 and get it to put the different values directly into the Cells
E6:E10 please.

Thanks in Advance.
All the Best.
Paul

On Aug 27, 6:30 pm, p45cal wrote:
Sub blaH6()
r = Split(Range("B2").Value, ",")
p = Split(r(3), ")=")
q = Split(r(0), "(")
Range("E6") = q(1)
Range("E7") = r(1)
Range("E8") = r(2)
Range("E9") = p(0)
Range("E10") = p(1)
'Range("E9:E10") = Application.WorksheetFunction.Transpose(p) 'replaces
'previous 2 lines if you want
End Sub

--
p45cal



"Ron Rosenfeld" wrote:
On Mon, 27 Aug 2007 09:50:58 -0700, Paul Black
wrote:

Hi everybody,

I have the following in Cell B2 :-

LD(24,6,3,6)=163

How can I extract with VBA the ...

24 < this could be 3 digits
6 < this could be 2 digits
3 < this could be 2 digits
6 < this could be 2 digits
163 < this could be 4 OR 5 digits

... so they go in Cells E6:E10 please. There will always be a comma
and the final figure will always follow the = sign.

Thanks in Advance.
All the Best.
Paul

One simple way is to write a short UDF allowing you to use a Regular Expression
to extract the values. You can then use this formula:

=ReExtr(cell_ref,regex,Index)

Where index is, in your example between 1 and 5 to extract the 1st, 2nd, etc
group of digits.

The regex to pick out a series of digits is "\d+".

So, with data in A1, you could enter

B1: =ReExtr($A$1,"\d+",COLUMNS($A:A))

and fill right to F1.

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:

===========================
Option Explicit
Function ReExtr(str As String, sPattern As String, Index As Long)
Dim re As Object
Dim mc As Object

Set re = CreateObject("vbscript.regexp")
re.Pattern = sPattern
re.Global = True

Set mc = re.Execute(str)

If Index <= mc.Count Then
ReExtr = mc(Index - 1)
Else
ReExtr = ""
End If
End Function
====================================

--ron- Hide quoted text -

- Show quoted text -

On Mon, 27 Aug 2007 11:01:42 -0700, Paul Black
wrote:

Thanks for the replies,

Is there any way using VBA code that I can pick up the string in Cell
B2 and get it to put the different values directly into the Cells
E6:E10 please.

Thanks in Advance.
All the Best.
Paul


Sure, you can write similar code as a sub:

=================================
Option Explicit
Sub ReExtr()
Dim re As Object
Dim mc As Object
Dim str As String
Dim src As Range
Dim dest As Range
Dim Index As Long

Const sPattern As String = "\d+"

Set re = CreateObject("vbscript.regexp")
re.Pattern = sPattern
re.Global = True

Set src = Range("B2")
Set dest = Range("E6")
str = src.Text

If re.test(str) = True Then
Set mc = re.Execute(str)
For Index = 1 To mc.Count
dest.Cells(1, Index) = mc(Index - 1)
Next Index
End If
End Sub
=====================================
--ron

That's exactly what my code does. Just run the macro BlaH6.
--
p45cal


"Paul Black" wrote:

Thanks for the replies,

Is there any way using VBA code that I can pick up the string in Cell
B2 and get it to put the different values directly into the Cells
E6:E10 please.

Thanks in Advance.
All the Best.
Paul

On Aug 27, 6:30 pm, p45cal wrote:
Sub blaH6()
r = Split(Range("B2").Value, ",")
p = Split(r(3), ")=")
q = Split(r(0), "(")
Range("E6") = q(1)
Range("E7") = r(1)
Range("E8") = r(2)
Range("E9") = p(0)
Range("E10") = p(1)
'Range("E9:E10") = Application.WorksheetFunction.Transpose(p) 'replaces
'previous 2 lines if you want
End Sub

--
p45cal



"Ron Rosenfeld" wrote:
On Mon, 27 Aug 2007 09:50:58 -0700, Paul Black
wrote:

Hi everybody,

I have the following in Cell B2 :-

LD(24,6,3,6)=163

How can I extract with VBA the ...

24 < this could be 3 digits
6 < this could be 2 digits
3 < this could be 2 digits
6 < this could be 2 digits
163 < this could be 4 OR 5 digits

... so they go in Cells E6:E10 please. There will always be a comma
and the final figure will always follow the = sign.

Thanks in Advance.
All the Best.
Paul

One simple way is to write a short UDF allowing you to use a Regular Expression
to extract the values. You can then use this formula:

=ReExtr(cell_ref,regex,Index)

Where index is, in your example between 1 and 5 to extract the 1st, 2nd, etc
group of digits.

The regex to pick out a series of digits is "\d+".

So, with data in A1, you could enter

B1: =ReExtr($A$1,"\d+",COLUMNS($A:A))

and fill right to F1.

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:

===========================
Option Explicit
Function ReExtr(str As String, sPattern As String, Index As Long)
Dim re As Object
Dim mc As Object

Set re = CreateObject("vbscript.regexp")
re.Pattern = sPattern
re.Global = True

Set mc = re.Execute(str)

If Index <= mc.Count Then
ReExtr = mc(Index - 1)
Else
ReExtr = ""
End If
End Function
====================================

--ron- Hide quoted text -

- Show quoted text -

Hi p45cal,

Thanks for the reply, I must have posted as you were. Your Sub does
indeed do what I want.
The thing is the Cell B2 is in a sheet named "Data" and I want the
results to go in a sheet named "Statistics" and in cells E6:E10.
I have had a go at putting this together but unfortunately it does not
work.
It would be appreciated if you could you give me a brief description
of what your code and variables are actually doing please.

Sub Test()
Dim r As Integer
Dim p As Integer
Dim q As Integer

Set rngDesign = Worksheets("Data").Range("B2")

r = (rngDesign.Value, ",")
p = Split(r(3), ")=")
q = Split(r(0), "(")

Worksheets("Statistics").Select
Range(""E6").Select
With Activecell
.Offset(0, 0).Value = q(1)
.Offset(1, 0).Value = r(1)
.Offset(2, 0).Value = r(2)
.Offset(3, 0).Value = p(0)
.Offset(4, 0).Value = p(1)
End With

End Sub

Thanks in Advance.
All the Best.
Paul

On Aug 27, 10:34 pm, p45cal wrote:
That's exactly what my code does. Just run the macro BlaH6.
--
p45cal



"Paul Black" wrote:
Thanks for the replies,

Is there any way using VBA code that I can pick up the string in Cell
B2 and get it to put the different values directly into the Cells
E6:E10 please.

Thanks in Advance.
All the Best.
Paul

On Aug 27, 6:30 pm, p45cal wrote:
Sub blaH6()
r = Split(Range("B2").Value, ",")
p = Split(r(3), ")=")
q = Split(r(0), "(")
Range("E6") = q(1)
Range("E7") = r(1)
Range("E8") = r(2)
Range("E9") = p(0)
Range("E10") = p(1)
'Range("E9:E10") = Application.WorksheetFunction.Transpose(p) 'replaces
'previous 2 lines if you want
End Sub

--
p45cal

"Ron Rosenfeld" wrote:
On Mon, 27 Aug 2007 09:50:58 -0700, Paul Black
wrote:

Hi everybody,

I have the following in Cell B2 :-

LD(24,6,3,6)=163

How can I extract with VBA the ...

24 < this could be 3 digits
6 < this could be 2 digits
3 < this could be 2 digits
6 < this could be 2 digits
163 < this could be 4 OR 5 digits

... so they go in Cells E6:E10 please. There will always be a comma
and the final figure will always follow the = sign.

Thanks in Advance.
All the Best.
Paul

One simple way is to write a short UDF allowing you to use a Regular Expression
to extract the values. You can then use this formula:

=ReExtr(cell_ref,regex,Index)

Where index is, in your example between 1 and 5 to extract the 1st, 2nd, etc
group of digits.

The regex to pick out a series of digits is "\d+".

So, with data in A1, you could enter

B1: =ReExtr($A$1,"\d+",COLUMNS($A:A))

and fill right to F1.

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:

===========================
Option Explicit
Function ReExtr(str As String, sPattern As String, Index As Long)
Dim re As Object
Dim mc As Object

Set re = CreateObject("vbscript.regexp")
re.Pattern = sPattern
re.Global = True

Set mc = re.Execute(str)

If Index <= mc.Count Then
ReExtr = mc(Index - 1)
Else
ReExtr = ""
End If
End Function
====================================

--ron- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -