Insert partial filename in cell A1

[ZzaqpazZ]

I need a way to insert the part of the file name after PO.
In the example below, 3351 is the work order number, between brackets is the customer name, then the description and then the PO number.
The problem is the PO number varies in length and could contain digits, dashes etc. so I need to return everything past PO .

Example;
3351 (Customer Name) Wave Guide Support - PO 2245

Should return the value 2245 in cell A1.

Any help would be appreciated

 

[Momentman]

You could do it this wayExcel 2010

	A	B
1	3351 (Customer Name) Wave Guide Support - PO 2245	2245

<colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead>
</thead><tbody>
</tbody>

Sheet5​

Worksheet Formulas Cell Formula B1 =MID(A1,FIND(" ",A1,FIND("PO",A1))+1,100) <thead> </thead><tbody> </tbody>

 

[ZzaqpazZ]

You could do it this wayExcel 2010

	A	B
1	3351 (Customer Name) Wave Guide Support - PO 2245	2245

<tbody>
</tbody>

Sheet5​

Worksheet Formulas Cell Formula B1 =MID(A1,FIND(" ",A1,FIND("PO",A1))+1,100) <tbody> </tbody>

<tbody>
</tbody>

Hi Momentman,
This formula implies that the file name be in one of the cells but I am trying to pull the values directly from the name of the file.
I also want to make sure not to return (.xlsx).
Thanks for your help.

 

[Momentman]

Ok, its the filename right? then maybe

Excel 2010
	A
1	2245
Sheet5

Cell Formulas
Range	Formula
A1	=TRIM(RIGHT(SUBSTITUTE(REPLACE(CELL("filename"),FIND(".",CELL("filename")),100,"")," ",REPT(" ",99)),99))

 

[ZzaqpazZ]

Ok, its the filename right? then maybe
Excel 2010

	A
1	2245

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet5​

Worksheet Formulas Cell Formula A1 =TRIM(RIGHT(SUBSTITUTE(REPLACE(CELL("filename"),FIND(".",CELL("filename")),100,"")," ",REPT(" ",99)),99)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Works perfect!!!
Thank you

 

[Momentman]

You are welcome. Thanks for the feedback

 

[ZzaqpazZ]

You are welcome. Thanks for the feedback

Hi Momentman,
A new problem has arisen from this formula.
Some of our client have initials (with a period) in their names which cause the wrong value to return in cell B1.
If I enter the file name 3351 (Customer Name) Wave Guide Support - PO 2245,
It returns 2245 as it should but...
If I enter the file name 3351 (Customer. Name) Wave Guide Support - PO 2245,
it returns (Customer

So it appears using a period in the file name creates a problem.
Any thoughts?
I'm not sure there is a solution to this as I think the period should represent the end of the name and so reading everything between PO and the period.
If it helps, the PO numbers are never longer that 20 or so digits at the most.

Any suggestions would be appreciated.
Thanks,
Martin

 

[Momentman]

It might be nice to have more possible examples just to be sure this formula covers all cases.

Am certain better formulas exist but here,

Excel 2010
	B
1	2245
Sheet1

Cell Formulas
Range	Formula
B1	=MID(CELL("filename"),FIND("- PO ",CELL("filename"))+5,FIND(".xl",CELL("filename"))-FIND("- PO ",CELL("filename"))-5)

 

[ZzaqpazZ]

That works perfectly.
I also tried whatever scenario I thought might come up and it works in every instance I tried.
Thanks again for your help.