I would like to make a loop with the formula defined in Excel with the VBA Function.
The function can be found here:

https://trumpexcel.com/extract-numbers-from-string-excel/

where the typical formula looks like:

  =GetNumeric(TEXTBEFORE(AD5,","))

although I don’t want to mess around with additional columns, so I decided to use them as the VBA formula.

My code could look like this:

 For jr = 5 To jlRow
 If bws.Cells(jr, "X").Value <> "" Then
 dws.Range("E1") = "=GetNumeric(TEXTBEFORE("&bws.Cells(jr, "AD").Value&",','))"
 End If
 Next jr

but unfortunately this approach doesn’t work with the apostrophes and quotes.
How could I manage with the formula such as this?