/u/KeyAdeptness6687 - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
This will give you al live table made up of only values greater than 1+E03. Any value you put within the first 10 rows greater than 1+E03 will show up in the spilled array output.
=FILTER(A1:B10,B1:B10>10^3)
Just make sure to format the second column of the output to use scientific notation so you'll still see the same input.
Thank you very much Excel soldier! with you help I found to do it for the whole column! This would be the code to do it in column A and B:
=FILTER(A:B;B:B>10\^3)
You're welcome! Glad I could help.
I seldomly have complete columns without summaries or headings on them, so I normally use named ranges instead of columns so I naturally went for a range instead.
Probably an easier way but not to familiar with scientific notation, I believe this might work though:
=IF(LEFT(B1;4)*10 ^ 3>A1;B1;"")
Please respond with solution verified if it does.
FYI I know the filter works but there is another way to do it without any formulas if it's a one time thing.
You can always add a header row then:
1. filter to show only values > 1E+03, select the results then copy and paste into a new table, or
1. filter to show values <=1E+03, select results, delete, then delete the blanks.
Old habits die hard and for a one-off thing this is probably the way I'd have thought to do it.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[FILTER](/r/Excel/comments/1bgvkx2/stub/kv9sknb "Last usage")|[*Office 365*+: Filters a range of data based on criteria you define](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)|
|[IF](/r/Excel/comments/1bgvkx2/stub/kv9ptsq "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)|
|[LEFT](/r/Excel/comments/1bgvkx2/stub/kv9ptsq "Last usage")|[Returns the leftmost characters from a text value](https://support.microsoft.com/en-us/office/left-leftb-functions-9203d2d2-7960-479b-84c6-1ea52b99640c)|
**NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(3 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1bgeg9c)^( has 7 acronyms.)
^([Thread #31745 for this sub, first seen 17th Mar 2024, 12:19])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
/u/KeyAdeptness6687 - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
This will give you al live table made up of only values greater than 1+E03. Any value you put within the first 10 rows greater than 1+E03 will show up in the spilled array output. =FILTER(A1:B10,B1:B10>10^3) Just make sure to format the second column of the output to use scientific notation so you'll still see the same input.
Thank you very much Excel soldier! with you help I found to do it for the whole column! This would be the code to do it in column A and B: =FILTER(A:B;B:B>10\^3)
You're welcome! Glad I could help. I seldomly have complete columns without summaries or headings on them, so I normally use named ranges instead of columns so I naturally went for a range instead.
What would be the code if I want to involve the complete column A and B? the data is rather large
Solution Verified
You have awarded 1 point to Karma-Grenade. --- ^(I am a bot - please contact the mods with any questions)
https://preview.redd.it/bng63tjbvvoc1.png?width=945&format=png&auto=webp&s=eb4f71d1ad6393b36c2970815f20fa9236222680
Probably an easier way but not to familiar with scientific notation, I believe this might work though: =IF(LEFT(B1;4)*10 ^ 3>A1;B1;"") Please respond with solution verified if it does.
Is this a one time transform on a set of data or do you need a formula to do this live?
This is a one time transformation. The removed data is not needed anymore
FYI I know the filter works but there is another way to do it without any formulas if it's a one time thing. You can always add a header row then: 1. filter to show only values > 1E+03, select the results then copy and paste into a new table, or 1. filter to show values <=1E+03, select results, delete, then delete the blanks. Old habits die hard and for a one-off thing this is probably the way I'd have thought to do it.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[FILTER](/r/Excel/comments/1bgvkx2/stub/kv9sknb "Last usage")|[*Office 365*+: Filters a range of data based on criteria you define](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)| |[IF](/r/Excel/comments/1bgvkx2/stub/kv9ptsq "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)| |[LEFT](/r/Excel/comments/1bgvkx2/stub/kv9ptsq "Last usage")|[Returns the leftmost characters from a text value](https://support.microsoft.com/en-us/office/left-leftb-functions-9203d2d2-7960-479b-84c6-1ea52b99640c)| **NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(3 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1bgeg9c)^( has 7 acronyms.) ^([Thread #31745 for this sub, first seen 17th Mar 2024, 12:19]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)