Make text strings uniform

[friendlyaccountant]

Hello all,

I am trying to make a specific column more uniform so I can create a pivot table. I would like to use formulas to do this. The problem is when I extract the data the original input is free form but somewhat standard. I am basically trying to extract two pieces of info "CNF" and "CO" + their accompanying number. If the string has CNF in it, it will automatically be a CNF and I would like to pull that number. Problem I'm having is sometimes there is a # or a " " or sometimes nothing. (ex. CNF18). The format I would like would be CNFxxx with no space or #.

So far my idea is to search the string for CNF extract that with a helper cell then pull the number. Any ideas?

Sample formulas: (A5 has original data)

[TABLE="width: 1125"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]A Column[/TD]
[TD]B Column[/TD]
[TD]C Column[/TD]
[TD]D Column[/TD]
[/TR]
[TR]
[TD]CNF11 to CO4[/TD]
[TD]=SUBSTITUTE(A6," ","")[/TD]
[TD]=TRIM(LEFT(B6,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B6&"0123456789"))-1))[/TD]
[TD]=TRIM(REPLACE(B6,1,LEN(C6),""))[/TD]
[/TR]
</tbody>[/TABLE]


Sample data:

[TABLE="width: 304"]
<colgroup><col></colgroup><tbody>[TR]
[TD]CNF 5[/TD]
[/TR]
[TR]
[TD]CNF3 CO1[/TD]
[/TR]
[TR]
[TD]Change Order 4[/TD]
[/TR]
[TR]
[TD]CNF11 to CO4[/TD]
[/TR]
[TR]
[TD]CNF32 CO6[/TD]
[/TR]
[TR]
[TD]CAR CORRECTION CAR79550[/TD]
[/TR]
[TR]
[TD]CNF8 CO2[/TD]
[/TR]
[TR]
[TD]CNF30 CO7[/TD]
[/TR]
[TR]
[TD]CO4[/TD]
[/TR]
[TR]
[TD]CNF4 CO1[/TD]
[/TR]
[TR]
[TD]CO3 to WO[/TD]
[/TR]
[TR]
[TD]CO3 to WO[/TD]
[/TR]
[TR]
[TD]CNF7 CO3[/TD]
[/TR]
[TR]
[TD]CNF6 CO3[/TD]
[/TR]
[TR]
[TD]CNF6 CO3[/TD]
[/TR]
[TR]
[TD]CNF6 CO3[/TD]
[/TR]
[TR]
[TD]CNF8 CO2[/TD]
[/TR]
[TR]
[TD]CNF6 CO2[/TD]
[/TR]
[TR]
[TD]CNF18 to CO5[/TD]
[/TR]
[TR]
[TD]CNF8 for CO1[/TD]
[/TR]
[TR]
[TD]CNF5 CO4[/TD]
[/TR]
[TR]
[TD]CNF6 CO5[/TD]
[/TR]
[TR]
[TD]CNF 4[/TD]
[/TR]
[TR]
[TD]CAR Correction 78325 CO4[/TD]
[/TR]
[TR]
[TD]CNF11 to CO4[/TD]
[/TR]
[TR]
[TD]CNF31 CO7[/TD]
[/TR]
[TR]
[TD]CO4 to Work Order under MSA[/TD]
[/TR]
[TR]
[TD]CNF 3[/TD]
[/TR]
[TR]
[TD]CNF12 for CO4[/TD]
[/TR]
[TR]
[TD]CNF5 CO3[/TD]
[/TR]
[TR]
[TD]CNF7 CO3[/TD]
[/TR]
[TR]
[TD]Work Order under MSA[/TD]
[/TR]
[TR]
[TD]CNF7 CO2[/TD]
[/TR]
[TR]
[TD]CNF4[/TD]
[/TR]
[TR]
[TD]CNF3[/TD]
[/TR]
</tbody>[/TABLE]

 

[friendlyaccountant]

Realized the results I'm looking for may not be clear.

End result for first few entries would be:

[TABLE="width: 114"]
<colgroup><col></colgroup><tbody>[TR]
[TD]CNF5[/TD]
[/TR]
[TR]
[TD]CNF3[/TD]
[/TR]
[TR]
[TD]CO4[/TD]
[/TR]
[TR]
[TD]CNF11[/TD]
[/TR]
[TR]
[TD]CNF32[/TD]
[/TR]
[TR]
[TD]CAR CORRECTION[/TD]
[/TR]
[TR]
[TD]CNF8[/TD]
[/TR]
[TR]
[TD]CNF30[/TD]
[/TR]
[TR]
[TD]CO4[/TD]
[/TR]
[TR]
[TD]CNF4[/TD]
[/TR]
</tbody>[/TABLE]