Extract Values from a String

[Paul Black]

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

 

[Joel]

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

 

[Ron Rosenfeld]

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

 

[p45cal]

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

 

[Paul Black]

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

 

[Ron Rosenfeld]

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

 

[p45cal]

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

 

[Paul Black]

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

 

[p45cal]

Here's your version, minimally adjusted to make it work:

Sub Test()
Dim r 'these three are not integers - Variants if you want
Dim p
Dim q

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

r = Split(rngDesign.Value, ",") 'you missed out 'Split'
p = Split(r(3), ")=")
q = Split(r(0), "(")

Worksheets("Statistics").Select
Range("E6").Select 'you had an extra double-quote mark
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

Now with a few refinements so that it doesn't matter what sheet is the
active sheet when you run it, nor does it change that active sheet.

Sub Test2()
Dim r
Dim p
Dim q

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

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

With Worksheets("Statistics").Range("E6")
..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
MsgBox "done" 'remove later
End Sub

re:"give me a brief description of what your code and variables are actually
doing please"

Well, the
r = Split(rngDesign.Value, ",")
splits the string "LD(24,6,3,6)=163" into an array of strings using the
comma as delimiter, so it results in:
r(0) being "LD(24"
r(1) being "6"
r(2) being "3"
r(3) being "6)=163"

So r(1) and r(2) can be used straight away, leaving r(0) and r(3) to sort out.
The next line:
p = Split(r(3), ")=")
splits r(3) into an array with 2 members, using the ")=" to delimit (hoping
you'll only ever have one of these character combinations in the string):
p(0) being "6"
p(1) being "163 "
both of which can be used 'as is'.

The next line:
q = Split(r(0), "(")
splits r(0) in the same way but uses "(" as a delimiter resulting in:
q(0) being "LD"
q(1) being "24"

Now there are 8 strings in 3 arrays; only the ones you want are then put
into the sheet. Excel seems happy to recognise the strings as numbers once on
the sheet.

 

[Paul Black]

Thanks everyone for the replies, it is appreciated.
p45cal, it work perfectly, you are a diamond.

All the Best.
Paul