Trying to extract a string between two string using MID and Search

[initialize]

So I have a column in excel that contain strings similar to "Policy Number: 123456, random random random string"
I m trying to create a new column with only the number after "Policy Number:"

=MID(F2,SEARCH("Policy Number:",F2)+14,SEARCH(",",F2)-SEARCH("Policy Number:",F2)-14). This doesnt work if after the policy number there is no comma. I also want it to show Null if there is no find.

Can someone give me some help?

 

[jtakw]

Hi,

How about this:

Excel 2010
	F	G	H
2	Policy Number: 123456, random random random string		123456
3	Policy Number: 234567 random random random string		234567
Sheet17

Cell Formulas
Range	Formula
H2	=SUBSTITUTE(MID(F2,FIND(":",F2)+2,FIND(",",SUBSTITUTE(F2," ",",",3))-FIND(":",F2)-1),",","")+0

This also converts the result to a real Number.

 

[VBA Geek]

is the police number always a number with no spaces in between and always of numeric characters?

Excel 2013/2016
	A	B	C
1	Policy Number: 123456 random random random string		123456
Sheet1

Cell Formulas
Range	Formula
C1	{=LEFT(MID(A1,LEN("Policy Number: ")+1,255),MATCH(TRUE,ISERROR(MID(MID(A1,LEN("Policy Number: ")+1,255),ROW(INDIRECT("1:"&LEN(MID(A1,LEN("Policy Number: ")+1,255)))),1)+0),0)-1)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Rick Rothstein]

Since it is a policy number, can we assume the format of that policy number is always the same (that is, all policy numbers are 6 digits long)? If so...

=MID(F2,SEARCH("Policy Number:",F2)+15,6)