Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Make text strings uniform

  1. #1
    New Member
    Join Date
    Oct 2016
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Make text strings uniform

    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 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),""))


    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

  2. #2
    New Member
    Join Date
    Oct 2016
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Make text strings uniform

    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •