New user here with a formula question... I need to pull a PO number out of a long description field.  Sometimes the PO is written as "PO#: xxxxx" with x being the 5 digit numbers.  Other times it's written as "PO# xxxxx", with no colon.  There's a lot of other information also embedded into this description field, mostly text.

I tried using a formula =Split(DESCRIPTION,1, "PO#") but the results look bad as every row returned data, even those without the "PO#" anywhere in the description.  How can I fix my formula, or use a different one entirely? 

Thanks for any help!

Could you please give me few example of full text in Description field.

Reagards, Ivan

Without more information I would try

=Left(Substitute(Substitute(Substitute(DESCRIPTION,"PO#",""),":","")," ",""),5)

It will delete "PO#" or "PO#:" or "PO# " or "PO#: " from the string begining and than take 5 characters

Regards, Ivan

If the string "PO#" is always the first thing in the field (if it's present), then you could use a MID(FIND()) formula combination to find the first space in the field, then take the succeeding five characters. To determine if the field actually has a PO number, just wrap the whole thing in an IF statement.

Thanks Ivan and Jonathan.  The Description field is inconsistent; the PO# can be first but it can also be embedded in the middle of the text.  Here are two examples: 

PO# 12345 ; ABC GROUP ; PRODUCT XYZ FOR JANE DOE ; JOHN DOE

ABC ; 67890 ;CURR: USD ;PO#: 12345 ;INV#: 99999 ;DESCR: JOHN DOE, 11/27/16 TO 11/27/17. TECH SERVICES REQUESTED.

First, I would write the IF statement to detect whether the PO# is present in the string.

=IF(FIND("PO#",DESCRIPTION),TRUE,FALSE)

If the string "PO#" is not found, then you want it to return an empty string. So,

=IF(FIND("PO#",DESCRIPTION),TRUE,"")

If the string "PO#" does exist, then you want to find the first space after that string.

=IF(FIND("PO#",DESCRIPTION),FIND(" ",DESCRIPTION,FIND("PO#",DESCRIPTION)),"")

Now, you want to use that space position to take the next five characters (your PO number).

=IF(FIND("PO#",DESCRIPTION),MID(DESCRIPTION,FIND(" ",DESCRIPTION,FIND("PO#",DESCRIPTION))+1,5),"")

Assuming I haven't messed up my syntax in there (), that formula will return the five-digit PO number no matter its location in the DESCRIPTION field. If there is no PO number in the DESCRIPTION field, it will return an empty string. That's a long, ugly formula, I know. If you need me to break it down further for you, just ask. 

I like this solution. It even solve situation there is not any "PO#" in the string. There is just one small mistake in syntax. Coreectly it should be:

=IF(FIND("PO#",DESCRIPTION)>0,MID(DESCRIPTION,FIND(" ",DESCRIPTION,FIND("PO#",DESCRIPTION))+1,5),"")

because FIND does not return logical value

You're right, it needed that ">0" in there. However, Apptio can interpret the numeric return values of FIND as logical values. A value of 0 will be interpreted as FALSE while any other numeric value (including negative values) will be interpreted as TRUE. This is similar to most other analytic systems, where the most common value of TRUE is -1.

I have never been succesful with using result of FIND function as logical value in V11. I was getting error messages when trying. Maybe it depend where you use it. 

Thanks for new info.