Make text strings uniform

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)

A ColumnB ColumnC ColumnD 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>
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Realized the results I'm looking for may not be clear.

End result for first few entries would be:

CNF5
CNF3
CO4
CNF11
CNF32
CAR CORRECTION
CNF8
CNF30
CO4
CNF4

<colgroup><col></colgroup><tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top