friendlyaccountant
New Member
- Joined
- Oct 5, 2016
- Messages
- 7
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)
<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
Sample data:
<colgroup><col></colgroup><tbody>
</tbody>
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)
A Column | B Column | C Column | D Column |
CNF11 to CO4 | =SUBSTITUTE(A6," ","") | =TRIM(LEFT(B6,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B6&"0123456789"))-1)) | =TRIM(REPLACE(B6,1,LEN(C6),"")) |
<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
Sample data:
CNF 5 |
CNF3 CO1 |
Change Order 4 |
CNF11 to CO4 |
CNF32 CO6 |
CAR CORRECTION CAR79550 |
CNF8 CO2 |
CNF30 CO7 |
CO4 |
CNF4 CO1 |
CO3 to WO |
CO3 to WO |
CNF7 CO3 |
CNF6 CO3 |
CNF6 CO3 |
CNF6 CO3 |
CNF8 CO2 |
CNF6 CO2 |
CNF18 to CO5 |
CNF8 for CO1 |
CNF5 CO4 |
CNF6 CO5 |
CNF 4 |
CAR Correction 78325 CO4 |
CNF11 to CO4 |
CNF31 CO7 |
CO4 to Work Order under MSA |
CNF 3 |
CNF12 for CO4 |
CNF5 CO3 |
CNF7 CO3 |
Work Order under MSA |
CNF7 CO2 |
CNF4 |
CNF3 |
<colgroup><col></colgroup><tbody>
</tbody>