Confilicting info in a value??

rkcim

Board Regular
Joined
Nov 19, 2003
Messages
87
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?
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,886
Office Version
  1. 365
Platform
  1. Windows
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:

Code:
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,093
Messages
5,768,047
Members
425,451
Latest member
JohnBrooksBiddle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top