Find & Replace values within a cell

[tcnt9176]

Is there a way to find values within the text of cells & convert it to currency?

If a cell has text like "Can you convert values like 11.23 to $11.23?" can you convert it to "Can you convert values like $11.23 to $11.23?"

Thanks for any information you can provide.

 

[Gary McMaster]

The sample below should work for the cited example. If there are no spaces before the number or the number is not meant to be currency this will fail. There may also be other causes of failure.

Please place the following text in cell "A1" before running the code.
"Can you convert values like 11.23 to $11.23?"

Hope it helps.

Gary

Public Sub Test()
 
Dim vSplit As Variant
Dim lCount As Long
 
vSplit = Split(ActiveSheet.Range("A1").Value, " ")
ActiveSheet.Range("A1").Value = ""
 
For lCount = LBound(vSplit) To UBound(vSplit)
    If IsNumeric(vSplit(lCount)) Then
        vSplit(lCount) = "$" & vSplit(lCount)
    End If
    ActiveSheet.Range("A1").Value = ActiveSheet.Range("A1").Value & " " & vSplit(lCount)
Next lCount
 
ActiveSheet.Range("A1").Value = Trim(Replace(ActiveSheet.Range("A1").Value, "$$", "$"))
 
End Sub