Hi all,
I'm pulling data from the web using a Macro to be used in calculations later. However, the data came in an abbreviated format such as 1.2B for one point two Billion or (3.3M) for negative three point three Million. I came up with a vba input (with help from the forum) for getting rid of the abbreviated letter and multiplying the result by it's corresponding abbreviated amount in full. The code is:

Sub ConvertTextToNumber()

Sheets("Income Statement").Select
LastRow = ActiveSheet.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
i = 1
Do
tempval = Range("B" & i).Value
If Right(tempval, 1) = "M" Then Range("B" & i).Value = CDec(Left(tempval, Len(tempval) - 1)) * 1000000
If Right(tempval, 1) = "B" Then Range("B" & i).Value = CDec(Left(tempval, Len(tempval) - 1)) * 1000000000
i = i + 1
Loop While i <= LastRow

'etc. etc. until all columns are converted and on all sheets.

It works great, however, the cells with parenthesis are left unchanged. How and in what format would I insert code to get rid of the parenthesis and replace it with a simple hyphen?