Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I "insert copied cells"? used to be "alt i e" still work | Excel Worksheet Functions | |||
correct syntax for nesting "if", "and", and "vlookup"....if possib | Excel Worksheet Functions | |||
How do I replace decimals starting with "." to "0." in excel? | Excel Discussion (Misc queries) | |||
Need correct IF(ISBLANK) formula when referencing whether one of twocells (OR) is blank, returning "no value" | Excel Worksheet Functions | |||
Simple? Formula for "for the week starting Monday May Xxth" | Excel Worksheet Functions |