Extracting string between characters

sykotex
sykotex used Ask the Experts™
on
Hi
I have an excel spreadsheet with various enteries like below
enfield_20190200_EQ
gb_20190200_EQ

I wish to extract just the numbers is there a way to extract the data between the underscores?
Or is there a way to use regular expressions
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2011
Commented:
For example:

=LEFT(MID(A1,FIND("_",A1)+1,LEN(A1)),FIND("_",MID(A1,FIND("_",A1)+1,LEN(A1)))-1)

assuming text in A1.
AlanConsultant
Commented:
Hi,

If the strings are all of that form (something, followed by underscore, followed by 8 numerics), then the following will extract the 8 numerics from A1:

=MID(A1,FIND("_",A1)+1,8)

HTH,

Alan.
AlanConsultant
Commented:
Or, if the numerics might be more or less than 8 long, you could use:

=LEFT(MID(A1,FIND("_",A1)+1,999),FIND("_",MID(A1,FIND("_",A1)+1,999))-1)

HTH,

Alan.
Just for fun...

=MID(A1,FIND("_",A1,1)+1,FIND("_",SUBSTITUTE(A1,"_"," ",1),1)-FIND("_",A1,1)-1)*1

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial