How do I find a 6 digit alphanumeric code that is part of a text stream

Ty Jones

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.

dojones

This is a way to do it. I'm showing it in parts for understanding, but you could combine it into a single formula.

1. find "SO" in the string. This is assuming it is always there.
   1. =FIND("SO", [Project Name]@row)
2. Find "A" starting at point SO was found
   1. =FIND("A", [Project Name]@row, [Find SO]@row)
3. Extract SO starting at point A is found
   1. =MID([Project Name]@row, [Find A]@row, 6)

Ty Jones

@dojones

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.

dojones

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