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

initialize

New Member
Joined
Dec 7, 2015
Messages
29
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?
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Hi,

How about this:


Excel 2010
FGH
2Policy Number: 123456, random random random string123456
3Policy Number: 234567 random random random string234567
Sheet17
Cell Formulas
RangeFormula
H2=SUBSTITUTE(MID(F2,FIND(":",F2)+2,FIND(",",SUBSTITUTE(F2," ",",",3))-FIND(":",F2)-1),",","")+0


This also converts the result to a real Number.
 
Last edited:
Upvote 0
is the police number always a number with no spaces in between and always of numeric characters?


Excel 2013/2016
ABC
1Policy Number: 123456 random random random string123456
Sheet1
Cell Formulas
RangeFormula
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.
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top