i have a google sheet that reads

Purchased on October 14, 2021 Selling  price:11250 Name #72894044

Is there a way to pull all 8-string numbers starting with #, but ignore everything else?

CodePudding user response：

You can make use of the REGEXTRACT function as such:
=REGEXEXTRACT(X, "(#\d{8})") where X is your input.

The pair of brackets (...) captures the first group.
Then it checks for the # specifically.
Followed by checking for any digits \d,
And checks it for x number of times {x}.

It's also good to take note that there are multiple ways of defining {...} which you can check out

Explanation

The formula returns the location of the # character by using FIND and afterwards it calculates the number of characters after # by making the subtraction and by using LEN. Afterwards, RIGHT returns the substring consisting of your desired value. ARRAYFORMULA is used considering the fact that you may have multiple entries which need the same formula applied to them.

Reference

• LEN;

• FIND;

• RIGHT;

• ARRAYFORMULA.