Confilicting info in a value??

[rkcim]

I have a cell that is formated as currency. Every once in a while an ^ arrow gets placed in front, due to data coming from a website. Since any functions can't use this data the arrow has to be removed. Here is an example:

On Sheet3(Web Query data gets updated)
C44
^$5.23

On Sheet1 B10 is referenced to C44
#VALUE!

Here are the two different ways the data is refreshed:
1. Each time I click a button on Sheet1 the data on Sheet3 is refereshed therefore refreshing any cells on Sheet1 that are referenced to cells on Sheet3.

2. Each time I click on a stock name in a combobox, which is on Sheet3, a new stock is added on Sheet1 along with its data from Sheet3. (A stock name will only appear in the combobox only if within the list of stock names on Sheet3 are not on Sheet1)

The quetion is how can I prevent the up ^ arrow from showing up in the data, while noting how the data is refreshed?

 

[rkcim]

Need Help?

 

[Joe4]

The following should take care of item #1. Unsure of #2...

Right click on the sheet tab name at the bottom of the page and select "View Code". Paste this code in the resulting box:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    On Error Resume Next
    Cells.Replace What:="^", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False
    On Error GoTo 0

End Sub

This should automatically look for and replace any "^" any time a cell is selected.