VBA Regular Expressions Find and Replace

[Stephen_IV]

I am trying to mimic a text editor that I am using for regular expressions in VBA. The text editor I am using is Sublime. I have a bunch of phrases and the numeric values I would like to put a dollar sign in front if I do not already have one. for example:

I have this:

The $40.00 dollar amount is ok with 10.00 down
The 87.00 is what we asked for.
Is Jon getting 3500.00 for his car.
Can I borrow 10.00 until Monday. I will pay you back 15.00.
Is the money for the hat 20.00?
The 11.00 dollar amount is 10.00 ok

using the regex pattern

(?<![$\d])(?=\d+\.\d\d)


and replacing with a $

I get this:

The $40.00 dollar amount is ok with $10.00 down
The $87.00 is what we asked for.
Is Jon getting $3500.00 for his car.
Can I borrow $10.00 until Monday. I will pay you back $15.00.
Is the money for the hat $20.00?
The $11.00 dollar amount is $10.00 ok

Is there a way to do this in VBA??

Thanks in advance!

 

[JoeMo]

Lightly tested, but returns the results you provided for the examples you provided.

Sub AddDollarSignToNumbers()
'assume strings are in col A starting in A1
Dim c As Range, V As Variant, i As Long
Application.ScreenUpdating = False
For Each c In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    V = Split(c.Value, " ")
    For i = 0 To UBound(V)
        If V(i) Like "[0-9]*" Then V(i) = "$" & V(i)
    Next i
    c.Value = Join(V, " ")
Next c
Application.ScreenUpdating = True
End Sub

 

[JLGWhiz]

You might also want to take a look at this site.

https://analystcave.com/excel-regex-tutorial/

 

[Stephen_IV]

Thank you JoeMo that definitely did the job. Appreciate it! Thank you also JLGWhiz for the link. Thank you both again!

 

[JLGWhiz]

Thank you JoeMo that definitely did the job. Appreciate it! Thank you also JLGWhiz for the link. Thank you both again!

Happy to help,]
Regards, JLG