Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Scientific Notation Issue E+000 etc.
Hi all,
I have a large list of numbers in column A, about 60k. Scattered throughout that list are numbers that Excel read as formulas since the fields were not converted to text ahead of time. I can have this list of numbers recreated again and this time in the correct format so that I should not have this problem again. What I need to do though is identify which of the numbers have been "fixed". Is there a way to take my new list and put it next to the old one and then run some kind of compare function that will allow me to isolate the data that has been fixed? I believe there are at least 1000 so I'd prefer not to do it manually. Thanks in advance for any guidance. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Scientific Notation Issue E+000 etc.
If you know the format then you can use something like
=TEXT(A1,"Format")=B1 to get TRUE or FALSE where Format is the format to convert A1 into the text format of B1 "BurghRocks" wrote: Hi all, I have a large list of numbers in column A, about 60k. Scattered throughout that list are numbers that Excel read as formulas since the fields were not converted to text ahead of time. I can have this list of numbers recreated again and this time in the correct format so that I should not have this problem again. What I need to do though is identify which of the numbers have been "fixed". Is there a way to take my new list and put it next to the old one and then run some kind of compare function that will allow me to isolate the data that has been fixed? I believe there are at least 1000 so I'd prefer not to do it manually. Thanks in advance for any guidance. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Scientific Notation Issue E+000 etc.
Hi Sheelo,
Would there be a way just to identify the numbers in column A that have the "+" in them and then only return the data in column B when the value in column A has the "+"? Thanks, Bill "Sheeloo" wrote: If you know the format then you can use something like =TEXT(A1,"Format")=B1 to get TRUE or FALSE where Format is the format to convert A1 into the text format of B1 "BurghRocks" wrote: Hi all, I have a large list of numbers in column A, about 60k. Scattered throughout that list are numbers that Excel read as formulas since the fields were not converted to text ahead of time. I can have this list of numbers recreated again and this time in the correct format so that I should not have this problem again. What I need to do though is identify which of the numbers have been "fixed". Is there a way to take my new list and put it next to the old one and then run some kind of compare function that will allow me to isolate the data that has been fixed? I believe there are at least 1000 so I'd prefer not to do it manually. Thanks in advance for any guidance. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Scientific Notation Issue E+000 etc.
You can try this
1. Press CTRL-' ' is on the same key as ~ on the top left corner of the keyboard. 2. Select Col A 3. Choose Edit|Replace and replace = with '= 4. Press CTRL-' again All cells with a formula in them will now have a = in the first place... You can then filter on 'Starts with =' "BurghRocks" wrote: Hi Sheelo, Would there be a way just to identify the numbers in column A that have the "+" in them and then only return the data in column B when the value in column A has the "+"? Thanks, Bill "Sheeloo" wrote: If you know the format then you can use something like =TEXT(A1,"Format")=B1 to get TRUE or FALSE where Format is the format to convert A1 into the text format of B1 "BurghRocks" wrote: Hi all, I have a large list of numbers in column A, about 60k. Scattered throughout that list are numbers that Excel read as formulas since the fields were not converted to text ahead of time. I can have this list of numbers recreated again and this time in the correct format so that I should not have this problem again. What I need to do though is identify which of the numbers have been "fixed". Is there a way to take my new list and put it next to the old one and then run some kind of compare function that will allow me to isolate the data that has been fixed? I believe there are at least 1000 so I'd prefer not to do it manually. Thanks in advance for any guidance. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Scientific Notation Issue E+000 etc.
Thanks Sheeloo, I'll give that a shot and see if it works. Seems like a
decent workaround. Thanks, Bill "Sheeloo" wrote: You can try this 1. Press CTRL-' ' is on the same key as ~ on the top left corner of the keyboard. 2. Select Col A 3. Choose Edit|Replace and replace = with '= 4. Press CTRL-' again All cells with a formula in them will now have a = in the first place... You can then filter on 'Starts with =' "BurghRocks" wrote: Hi Sheelo, Would there be a way just to identify the numbers in column A that have the "+" in them and then only return the data in column B when the value in column A has the "+"? Thanks, Bill "Sheeloo" wrote: If you know the format then you can use something like =TEXT(A1,"Format")=B1 to get TRUE or FALSE where Format is the format to convert A1 into the text format of B1 "BurghRocks" wrote: Hi all, I have a large list of numbers in column A, about 60k. Scattered throughout that list are numbers that Excel read as formulas since the fields were not converted to text ahead of time. I can have this list of numbers recreated again and this time in the correct format so that I should not have this problem again. What I need to do though is identify which of the numbers have been "fixed". Is there a way to take my new list and put it next to the old one and then run some kind of compare function that will allow me to isolate the data that has been fixed? I believe there are at least 1000 so I'd prefer not to do it manually. Thanks in advance for any guidance. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Scientific Notation Issue E+000 etc.
Excel will change any number with more than 11 digits to Scientific
Notation. Enter in B1 =LEN(A1) Double-click the fill handle to copy down. Filter on column B for greater than 11 to reveal those numbers with Scientific Notation. Gord Dibben MS Excel MVP On Wed, 5 Nov 2008 08:42:20 -0800, BurghRocks wrote: Hi Sheelo, Would there be a way just to identify the numbers in column A that have the "+" in them and then only return the data in column B when the value in column A has the "+"? Thanks, Bill "Sheeloo" wrote: If you know the format then you can use something like =TEXT(A1,"Format")=B1 to get TRUE or FALSE where Format is the format to convert A1 into the text format of B1 "BurghRocks" wrote: Hi all, I have a large list of numbers in column A, about 60k. Scattered throughout that list are numbers that Excel read as formulas since the fields were not converted to text ahead of time. I can have this list of numbers recreated again and this time in the correct format so that I should not have this problem again. What I need to do though is identify which of the numbers have been "fixed". Is there a way to take my new list and put it next to the old one and then run some kind of compare function that will allow me to isolate the data that has been fixed? I believe there are at least 1000 so I'd prefer not to do it manually. Thanks in advance for any guidance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Scientific notation of CSV | Excel Discussion (Misc queries) | |||
Scientific notation | Excel Worksheet Functions | |||
Scientific notation from a csv file | Excel Discussion (Misc queries) | |||
scientific notation | Excel Discussion (Misc queries) | |||
Scientific notation | Excel Discussion (Misc queries) |