Formula to correct copied option tickers starting with "+"
 

I copy financial data from my brokers website and paste it as text into a
spreadsheet. Look-up formulas reference the downloaded information. My Broker
precedes option tickers with a + sign, and Excel thinks that it is a formula.
Example: The copied text +EDTG, is displayed in the formula bar as =+EDTG
and the cell shows the #NAME? error.
I currently use the replace tool to remove the =+ operators, which makes the
entry usable, but would like to know if there is a way to do this with a
formula.
Any help is greatly appreciated.
BTW, I use Excel 2003

Formula to correct copied option tickers starting with "+"
 

Hi Rolf,

Since you said you use lookup formula, I suggest you include this formula in
the lookup result: = right(lookupresult,len(lookupresult)-1)

Thank you.

"Rolf" wrote:

I copy financial data from my brokers website and paste it as text into a
spreadsheet. Look-up formulas reference the downloaded information. My Broker
precedes option tickers with a + sign, and Excel thinks that it is a formula.
Example: The copied text +EDTG, is displayed in the formula bar as =+EDTG
and the cell shows the #NAME? error.
I currently use the replace tool to remove the =+ operators, which makes the
entry usable, but would like to know if there is a way to do this with a
formula.
Any help is greatly appreciated.
BTW, I use Excel 2003

Formula to correct copied option tickers starting with "+"
 

Hi eksh
Thanks for the reply. I had already tried your suggestion without success,
since any formula referencing a cell containing an error value will return
another error value.
Excel regards the text +EDTG as a formula and returns the error #Name?.





"eksh" wrote:

Hi Rolf,

Since you said you use lookup formula, I suggest you include this formula in
the lookup result: = right(lookupresult,len(lookupresult)-1)

Thank you.

"Rolf" wrote:

I copy financial data from my brokers website and paste it as text into a
spreadsheet. Look-up formulas reference the downloaded information. My Broker
precedes option tickers with a + sign, and Excel thinks that it is a formula.
Example: The copied text +EDTG, is displayed in the formula bar as =+EDTG
and the cell shows the #NAME? error.
I currently use the replace tool to remove the =+ operators, which makes the
entry usable, but would like to know if there is a way to do this with a
formula.
Any help is greatly appreciated.
BTW, I use Excel 2003

Formula to correct copied option tickers starting with "+"
 

Hi Rolf,

Sorry I misunderstood your problem before.
In this case you may preset the cells format to text. But if you have
numeric value in that cell it will become text as well. I'm not sure your
current method better or this one.

Thanks & Regards,
Evie

"Rolf" wrote:

Hi eksh
Thanks for the reply. I had already tried your suggestion without success,
since any formula referencing a cell containing an error value will return
another error value.
Excel regards the text +EDTG as a formula and returns the error #Name?.





"eksh" wrote:

Hi Rolf,

Since you said you use lookup formula, I suggest you include this formula in
the lookup result: = right(lookupresult,len(lookupresult)-1)

Thank you.

"Rolf" wrote:

I copy financial data from my brokers website and paste it as text into a
spreadsheet. Look-up formulas reference the downloaded information. My Broker
precedes option tickers with a + sign, and Excel thinks that it is a formula.
Example: The copied text +EDTG, is displayed in the formula bar as =+EDTG
and the cell shows the #NAME? error.
I currently use the replace tool to remove the =+ operators, which makes the
entry usable, but would like to know if there is a way to do this with a
formula.
Any help is greatly appreciated.
BTW, I use Excel 2003

Formula to correct copied option tickers starting with "+"
 

Hi Evie,

Thanks again for the reply. Your suggestion works fine. I can now use the
copied data in a formulas, by eliminating the extra characters (plus sign and
space). It never occurred to me, since I thought that pasting the data as
text would accomplish the same. Again, Thanks, you were a great help.

Rolf



"eksh" wrote:

Hi Rolf,

Sorry I misunderstood your problem before.
In this case you may preset the cells format to text. But if you have
numeric value in that cell it will become text as well. I'm not sure your
current method better or this one.

Thanks & Regards,
Evie

"Rolf" wrote:

Hi eksh
Thanks for the reply. I had already tried your suggestion without success,
since any formula referencing a cell containing an error value will return
another error value.
Excel regards the text +EDTG as a formula and returns the error #Name?.





"eksh" wrote:

Hi Rolf,

Since you said you use lookup formula, I suggest you include this formula in
the lookup result: = right(lookupresult,len(lookupresult)-1)

Thank you.

"Rolf" wrote:

I copy financial data from my brokers website and paste it as text into a
spreadsheet. Look-up formulas reference the downloaded information. My Broker
precedes option tickers with a + sign, and Excel thinks that it is a formula.
Example: The copied text +EDTG, is displayed in the formula bar as =+EDTG
and the cell shows the #NAME? error.
I currently use the replace tool to remove the =+ operators, which makes the
entry usable, but would like to know if there is a way to do this with a
formula.
Any help is greatly appreciated.
BTW, I use Excel 2003