Help With Extract or Similar Solution Needed...

[Ryan_Wagner]

 Hi everyone. This is an annoying question, I am sure. I am wondering how I can best get the number after the "MVTrapID#" in this screenshot. In the attached example, I would need "73022" to be the result, with no other text/numbers...This "comments" column is from a read-only BigQuery table, and I can't do anything to alter the data coming in.

Would appreciate help! I am assuming that EXTRACT or SUBSTITUTE come into play here, but I'm having no luck with my attempts.

[dbaum]

It all depends on whether there's any reliable pattern in the data. If so, you should be able to combine functions to home in on the number you need. Techniques that might be relevant include:

• SPLIT the value (e.g., at the "#" symbol)
• Use LEFT or RIGHT to truncate the value
• EXTRACTNUMBERS from the value
• FIND a position in the value and use MID to extract a segment at that position

[Ryan_Wagner]

Thanks, @dbaum. There is definitely regularity in the data, so hopefully that makes it work. 

I tried using combinations of those expressions, but I had no luck...

I have wanted to use EXTRACT many times, but it seems to be above my level of intelligence. If you find yourself bored or in need of some big "thank you"'s, I probably won't have this solved anytime soon...

[dbaum · Accepted solution]

LEFT(INDEX(SPLIT([comments], "# "), 2), 5)

[Ryan_Wagner]

That did it! Thank you!