How to extract values from a string and store them in a variable

[Matador_24]

Hello,

I am currently building a macro to extract a string from another string and store it in a variable, but I obtain the error: COMPILE ERROR: SUB OR FUNCTION NOT DEFINED and it highlights the word SEARCH in the formula.
What am I missing or how can I translate them into VBA language?
See formulas:

Sub Extractwords()


Dim Property As String


Property = Mid(A1, Search("(", A1) + 1, Search(")", A1) - Search("(", A1) - 1)




End Sub

Moreover, I have these other two formulas that I need to use in another two variables and have the same issue:

=MID(A2,SEARCH("-",A2)+1,SEARCH("Books",A2)-SEARCH("-",A2)-2)

=IF(SUM(LEN(B2)-LEN(SUBSTITUTE(B2,{"0","1","2","3","4","5","6","7","8","9"},"")))>0, SUMPRODUCT(MID(0&B2, LARGE(INDEX(ISNUMBER(--MID(B2, ROW(INDIRECT("1:"&LEN(B2))), 1)) * ROW(INDIRECT("1:"&LEN(B2))), 0), ROW(INDIRECT("1:"&LEN(B2))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(B2)))/10),"")

basically this is what the formulas do:

1st formula
String: SP 103 E 86 LLC (103e86st)
Formula:
=Mid(A1, Search("(", A1) + 1, Search(")", A1) - Search("(", A1) - 1)
Result: 103e86st

2nd formula:
String: Trial Balance For The Period January 2018 - March 2018 Books = Accrual, Eliminations
Formula:
=MID(A2,SEARCH("-",A2)+1,SEARCH("Books",A2)-SEARCH("-",A2)-2)
Result: March

3rd formula:
String: Trial Balance For The Period January 2018 - March 2018 Books = Accrual, Eliminations
Formula:
=IF(SUM(LEN(B2)-LEN(SUBSTITUTE(B2,{"0","1","2","3","4","5","6","7","8","9"},"")))>0, SUMPRODUCT(MID(0&B2, LARGE(INDEX(ISNUMBER(--MID(B2, ROW(INDIRECT("1:"&LEN(B2))), 1)) * ROW(INDIRECT("1:"&LEN(B2))), 0), ROW(INDIRECT("1:"&LEN(B2))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(B2)))/10),"")
Result: 2018

I tested the formulas in excel and they work,

Thanks in advance!!

Luis

 

[Kenneth Hobson]

1. SEARCH() is not a member of the VBA object.
a. One way to use the built-in SEARCH() method is to use WorksheetFunction.Search.
2. A1 is a value or did you want the value of cell A1? If the latter, see the commented ways one can do it.

e.g.

'=MID(A1, SEARCH("(", A1) + 1, SEARCH(")", A1) - SEARCH("(", A1) - 1)
Sub Extractwords()
  Dim Property As String, A1 As String
  A1 = "SP 103 E 86 LLC (103e86st)"
  'A1 = [A1]
  'A1=Range("A1").Value2
  'A1=Cells(1, "A").Value2
  Property = Mid(A1, WorksheetFunction.Search("(", A1) + 1, _
    WorksheetFunction.Search(")", A1) - WorksheetFunction.Search("(", A1) - 1)
  MsgBox Property
End Sub

Other ways you can use built-in functions might be Evaluate().

Other VBA methods like Instr() or InStrRev() might be used rather than WorksheetFunction.SEARCH. VBA is the default object in the VBE. You can easily find those by F2 in the VBE or type "VBA." in VBE.

 

[Matador_24]

Hello Thanks for the answer! it works for formulas 1 and 2, do you know why it does not work for formula 3?

Year = IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{"0","1","2","3","4","5","6","7","8","9"},"")))>0, SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1)) * ROW(INDIRECT("1:"&LEN(A2))), 0), ROW(INDIRECT("1:"&LEN(A2))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(A2)))/10),"")

It says COMPILE ERROR: SYNTAX ERROR

Thanks!

Luis

 

[Kenneth Hobson]

You are trying to use VBA as standard built-in formula functions as I explained earlier. It is better to say what you want to accomplish rather than trying to make a VBA solution just using built-in formula functions. That can be done of course, mostly.

If you insist on using formulas in VBA, Evaluate() is another way to do it. The trick is concatenating strings to get your formula for Evaluate() and handle embedded quotes. e.g.

Sub Main()
  Dim s As String, LastFirst As String

  Range("A1").Value = "Hobson"
  '=A1 & ", " & "Ken"
  s = "A1 & "", "" & ""Ken"""
  'check that formula string is correct. Note embedded quotes.
  Debug.Print s 'Results in Immediate Window
  LastFirst = Evaluate(s)
  MsgBox LastFirst
End Sub

 

[Matador_24]

Hello,

Yes, sorry, I was not very clear in the first post, what I want the third variable to grab from the string, it would be good if I can make it work as formula 1 and 2 that you mentioned, basically what I need is to extract is the Year of the closing month (2018), thus, the one just before 'Books' (e.g. 2018) from the string below, the string length will be different each time because the month will be different, the formula below assumes the string is contained in cell B2:

3rd formula:
String sample: Trial Balance For The Period January 2017 - March 2018 Books = Accrual, Eliminations

This is the formula I have used before in excel but as you said, it does not work in VBA:
=IF(SUM(LEN(B2)-LEN(SUBSTITUTE(B2,{"0","1","2","3","4","5","6","7","8","9"},"")))>0, SUMPRODUCT(MID(0&B2, LARGE(INDEX(ISNUMBER(--MID(B2, ROW(INDIRECT("1:"&LEN(B2))), 1)) * ROW(INDIRECT("1:"&LEN(B2))), 0), ROW(INDIRECT("1:"&LEN(B2))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(B2)))/10),"")

Result desired to be stored in the variable: 2018

Thanks for the help!

Luis

 

[Fluff]

How about

Sub Extractwords()
   Dim p1 As String, p2 As String, p3 As String
   
   p1 = Split(Split(Range("A1").Value, "(")(1), ")")(0)
   p2 = Split(Split(Range("A2").Value, "- ")(1), " ")(0)
   p3 = Split(Split(Range("B2").Value, "- ")(1), " ")(1)
End Sub

 

[Matador_24]

How about

Sub Extractwords()
   Dim p1 As String, p2 As String, p3 As String
   
   p1 = Split(Split(Range("A1").Value, "(")(1), ")")(0)
   p2 = Split(Split(Range("A2").Value, "- ")(1), " ")(0)
   p3 = Split(Split(Range("B2").Value, "- ")(1), " ")(1)
End Sub

Hello Fluff it works perfectly... exactly what I needed! thanks!!!!

Thanks Kenneth for your help, it is useful as well to understand my mistakes!

 

[Fluff]

Glad we could help & thanks for the feedback