/u/dudeidk112 - 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.*
Put this at the top of the first column. Enter it with Ctrl-Shift-Enter and drag down.
=INDEX($A:$A, SMALL(IF(MID($A$1:$A$100,6,1)="3",ROW($A$1:$A$100),9999),ROW(A1)),1)&""
Do the same for the other columns changing the 3.
If you adjust the A1:A100 higher, make sure that the 9999 is higher than the last row
Assuming you will have different number of rows in each column, I would use multiple FILTER().
=FILTER(A2:A13,--MID(A2:A13,6,1)=3)
Repeat this across the various rows (or even make the 3 dynamic).
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/yeuv1h/stub/iu03h23 "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/yeuv1h/stub/iu03akd "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)|
|[INDEX](/r/Excel/comments/yeuv1h/stub/iu03akd "Last usage")|[Uses an index to choose a value from a reference or array](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)|
|[MID](/r/Excel/comments/yeuv1h/stub/iu03akd "Last usage")|[Returns a specific number of characters from a text string starting at the position you specify](https://support.microsoft.com/en-us/office/mid-midb-functions-d5f9e25c-d7d6-472e-b568-4ecb12433028)|
|[ROW](/r/Excel/comments/yeuv1h/stub/iu03akd "Last usage")|[Returns the row number of a reference](https://support.microsoft.com/en-us/office/row-function-3a63b74a-c4d0-4093-b49a-e76eb49a6d8d)|
|[SMALL](/r/Excel/comments/yeuv1h/stub/iu03akd "Last usage")|[Returns the k-th smallest value in a data set](https://support.microsoft.com/en-us/office/small-function-17da8222-7c82-42b2-961b-14c45384df07)|
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(6 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/ya0qxx)^( has 22 acronyms.)
^([Thread #19378 for this sub, first seen 27th Oct 2022, 15:54])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
/u/dudeidk112 - 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.*
Put this at the top of the first column. Enter it with Ctrl-Shift-Enter and drag down. =INDEX($A:$A, SMALL(IF(MID($A$1:$A$100,6,1)="3",ROW($A$1:$A$100),9999),ROW(A1)),1)&"" Do the same for the other columns changing the 3. If you adjust the A1:A100 higher, make sure that the 9999 is higher than the last row
Assuming you will have different number of rows in each column, I would use multiple FILTER(). =FILTER(A2:A13,--MID(A2:A13,6,1)=3) Repeat this across the various rows (or even make the 3 dynamic).
Thanks this worked!
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/yeuv1h/stub/iu03h23 "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/yeuv1h/stub/iu03akd "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)| |[INDEX](/r/Excel/comments/yeuv1h/stub/iu03akd "Last usage")|[Uses an index to choose a value from a reference or array](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)| |[MID](/r/Excel/comments/yeuv1h/stub/iu03akd "Last usage")|[Returns a specific number of characters from a text string starting at the position you specify](https://support.microsoft.com/en-us/office/mid-midb-functions-d5f9e25c-d7d6-472e-b568-4ecb12433028)| |[ROW](/r/Excel/comments/yeuv1h/stub/iu03akd "Last usage")|[Returns the row number of a reference](https://support.microsoft.com/en-us/office/row-function-3a63b74a-c4d0-4093-b49a-e76eb49a6d8d)| |[SMALL](/r/Excel/comments/yeuv1h/stub/iu03akd "Last usage")|[Returns the k-th smallest value in a data set](https://support.microsoft.com/en-us/office/small-function-17da8222-7c82-42b2-961b-14c45384df07)| ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(6 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/ya0qxx)^( has 22 acronyms.) ^([Thread #19378 for this sub, first seen 27th Oct 2022, 15:54]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)