Extract Serial Number from middle of text string when surrounded by spaces

[hilldogwater]

HELP. I am trying to find the right formula to pull out just a document number/serial number out of a string of text.

EXAMPLE:

Cell A3 Contains:
"Refer to WSC.RPP.52-024 for marking requirements."

I want to make a formula that will just return "WSC.RPP.52-024".


It seems to be tricky because it is surrounded by spaces and not unique characters.

What I have now is:

=MID(A3,SEARCH("WSC",A3,1)-1,SEARCH(" ",A3,SEARCH(" ",A3,SEARCH("WSC",A3,1)-0)+0)-SEARCH(" ",A3,1))

But it returns: " WSC.RPP.52-024 fo"



PLEASE HELP!! THANK YOU!!

 

[jtakw]

Hi,

Does the data you want extracted ALWAYS start with "WSC", and is ALWAYS the Third group of characters not separated by any spaces and end with a space?

Try this:

Excel 2010
	A	B
3	Refer to WSC.RPP.52-024 for marking requirements.	WSC.RPP.52-024
Sheet1

Cell Formulas
Range	Formula
B3	=MID(A3,FIND("WSC",A3),FIND("^",SUBSTITUTE(A3," ","^",3))-FIND("WSC",A3))

 

[Jonmo1]

Does the string ALWAYS begin with "Refer to " and end with " for marking requirements." ?

 

[hilldogwater]

Hi,

Does the data you want extracted ALWAYS start with "WSC", and is ALWAYS the Third group of characters not separated by any spaces and end with a space?

Yes, it will ALWAYS start with "WSC", however sometimes it is the Fourth group. Thanks!

 

[hilldogwater]

No, there is no rhyme or reason to what comes before or after the Document Number.

 

[jtakw]

Yes, it will ALWAYS start with "WSC", however sometimes it is the Fourth group. Thanks!

Then does it always end with a hyphen followed by 3 numbers? (e.g. -024, -999, etc.)

 

[MarcelBeug]

My suggestion

=MID(A3,SEARCH("WSC",A3),SEARCH(" ",A3&" ",SEARCH("WSC",A3))-SEARCH("WSC",A3))

 

[hilldogwater]

Yes it will.

 

[hilldogwater]

The only thing I should mention is sometimes it is at the end of the text and therefore will not have a " " after the Document number.

 

[jtakw]

Yes it will.

Then you can use this:

Excel 2010
	A	B
3	Refer to WSC.RPP.52-024 for marking requirements.	WSC.RPP.52-024
Sheet1

Cell Formulas
Range	Formula
B3	=MID(A3,SEARCH("WSC",A3),FIND("-",A3)+4-SEARCH("WSC",A3))