How do I find a 6 digit alphanumeric code that is part of a text stream
Here's my problem I have a large database of information in a Project Name column. You can see in each cell there is a six digit alphanumeric code that starts with the "A". The codes location is not consistent, nor is the text and/or spaces around the code. I need a formula that will find this code and return it to another cell in the sheet. I've already created multiple helper columns to try and find it based on the preceding conditions of the alphanumeric code but there are just too many to manage.
Answers

This is a way to do it. I'm showing it in parts for understanding, but you could combine it into a single formula.
 find "SO" in the string. This is assuming it is always there.
 =FIND("SO", [Project Name]@row)
 Find "A" starting at point SO was found
 =FIND("A", [Project Name]@row, [Find SO]@row)
 Extract SO starting at point A is found
 =MID([Project Name]@row, [Find A]@row, 6)
 find "SO" in the string. This is assuming it is always there.

Thank you for the solution. Unfortunately the SO is not always present either. The only thing that is constant is it is the 6 digit alphanumeric code always starts with A and the code always has a space after it. I had created a similar "Find" "mid" formula to identify the SO which is how I found their were other options that created issues that only a wildcard function might fix, which Smartsheet doesn't support.

There's still a way, it's just more complicated. So you are looking for any string that starts with an A and has 5 numbers after it. Because there could be multiple A's in the string, you will have to search it multiple times to get to one with 5 numbers after it. Try this.
Find A!  First occurrence of A:
=FIND("A", [Project Name]@row)
A1 Value  Extract 5 character from A1:
=MID([Project Name]@row, [Find A1]@row + 1, 5)
A1 SO  record the value or 0
=IFERROR(VALUE([A1 Value]@row), 0)
Find A2  Find next A in string
=FIND("A", [Project Name]@row, [Find A1]@row + 1)
A2 Value  Extract 5 characters from second A
=IF([A1 SO]@row > 0, 0, MID([Project Name]@row, [Find A2]@row + 1, 5))
A2 SO
=IFERROR(VALUE([A2 Value]@row), 0)
A3 Value
=IF([A2 SO]@row > 0, 0, MID([Project Name]@row, [Find A3]@row + 1, 5))
A3 SO
=IFERROR(VALUE([A3 Value]@row), 0)
SO Num  Check to see which found the value
=IF([A1 SO]@row > 0, [A1 SO]@row, IF([A2 SO]@row > 0, [A2 SO]@row, IF([A3 SO]@row > 0, [A3 SO]@row, "Not Found")))
SO  Add "A" prefix
="A" + [SO Num]@row
Add more if you want to search through A4, A5, etc
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 349 Global Discussions
 199 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 133 Brandfolder
 127 Just for fun
 127 Community Job Board
 455 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!