10 Replies Latest reply: Aug 11, 2016 10:36 AM by Austin Perkins RSS

    Concatenate two columns

    Ravi Paw

      Hi all

       

      I am in need of some help, I did try to find an appropriate answer but could not find one. I have the following fields that I have captured and would like to concatenate the following fields CONCENTRATIONRAW1 with CONCENTRATIONRAW2 (Both are of a character nature in detail template). in to a new column called CONCENTRATION

       

       

       

      I would like to concatenate the them to have a result such as below;

       

      33]-[46

      43]-[46

      49]-[52

      82 ]

      82 ]

      0.037

       

      Is this possible?

       

      Many thanks in advance

        • Re: Concatenate two columns
          Austin Perkins

          Ravi,

           

          You should be able to accomplish this by adding a calculated field with the formula being: [CONCENTRATIONRAW1] + [CONCENTRATIONRAW2]

           

          Just name your field CONCENTRATION and you'll be good to go. Once the calculated field is created, you can set your table up to hid the RAW1 and RAW2 columns and only show the concatenated version.

           

          Austin

          • Re: Concatenate two columns
            Chris Porthouse

            You have a couple of options.  You will need to create a new formula field called "CONCENTRATION" and can use either of these:

            1) Use the Concatenate_XL() function that will allow you to concatenate several string:

                 Concatenate_XL(CONCENTRATIONRAW1,CONCENTRATIONRAW2) or

            2) Simply type out the two fields you are trying to concatenate with the plus "+" sign between them:

                 CONCENTRATIONRAW1 + CONCENTRATIONRAW2

             

            Hope this helps

            • Re: Concatenate two columns
              Ravi Paw

              Thanks Austin and Chris. How simple was that . I am not very excel savvy, so if OK would like some further assistance.

              I have my fields concatenated and would now like to create a final column that just includes the numbers (i.e. without the Square brackets and hypen)

               

              Here is my column that was concatenated

              I would like to have them to have a result such as below;

               

              33 46

              43 46

              49 52

              82

              82

              0.037

               

              Is this possible?

              Thank you

                • Re: Concatenate two columns
                  Austin Perkins

                  Ravi,

                   

                  You certainly have some options in doing this. The easiest thing to do would be to select the concatenation column, do a Find and Replace with Find: ]-[ and Replace With being a space, then click Replace All. Then repeat the process but change Find to ] and change Replace With to nothing, leave it blank, then click Replace All (be sure before you click Replace All that you have just the concatenation column selected or else it will mess with your other data.

                   

                  Another option is to download the Excel Add-In ASAP Utilities which has an "Advanced Character Removal Tool" which would allow you to remove specific characters from cells.

                   

                  I believe your better bet would be to redo your import template so that these characters aren't even imported initially. If you'd like to share your file and import template, I'd be happy to share some tips on if this would be possible.

                   

                  Austin

                    • Re: Concatenate two columns
                      Grant Perkins

                      "I believe your better bet would be to redo your import template so that these characters aren't even imported initially. If you'd like to share your file and import template, I'd be happy to share some tips on if this would be possible."

                       

                      Agreed.

                       

                      There are many options for formatting and conditional formatting using Functions in Monarch Formula Fields.

                       

                      Potentially there are so many approaches it might at first seem a little confusing.

                       

                      The offer to create some examples is a good one.

                       

                      In general when becoming familiar with the techniques I recommend a "one step at a time" approach. Create a new field that applies one change. Make the formula for the next change, where required, in another new field that uses the output from the first formula fields .... and so on.

                       

                      When you are happy that everything works as intended simply copy the formula from the first calculated new field to the second in order to replace the [field name] referenced in the second field with the full formula from the first field.

                       

                      Repeat that process with each step along chain to the final output field and then delete (or maybe HIDE so you can go back and see what was done easily later) the fields that are no longer required. Also document the steps in some comments along with the formula.

                       

                      HTH.

                       

                      Grant

                      • Re: Concatenate two columns
                        Ravi Paw

                        Hi Austin

                         

                        Please find attached to this email an attachment of my report for your understanding.

                         

                        Thanks

                        Ravi

                    • Re: Concatenate two columns
                      Ravi Paw

                      Hi Guys - Thanks for your info. Here is what my report looks like. I posted this on another forum called "Need help capturing data" and got some useful tips on capturing the data but unfortunately wasn't what I needed. Here's why; the data I modelled was OK and captured what I needed by capturing all of the data under "Pro-Zone" in one cell and then work from there i.e. keep splitting the data out into individual fields using formula fields (you will able to see my working on that Thread) which is great, however after modelling the data goes into a SQL table (after automation process) ready for transported into an electronic worksheet. The SQL has been set up in a very rigid way so it expects data to be in a certain way. The modelling I did in the forum "Need help capturing data" certainly would not accommodate the SQL therefore I am back to the drawing board.

                       

                      I did try to capture the data in a detail template without the square brackets to begin with but was unfortunate.

                       

                       

                      ITEM#1        [KFree]                                                                              Co-ordinate           

                        Units[mg/L        ] Dec.P[2]                                                                        Y=[1.000]X+[0.000] 

                                                                                                                          Mon.    Span[3.000] ZeroP[1

                                                                                                                                                 

                      Calibration                                                                                                               

                        Type[Logit 2                ]                                                                    Read    Main[53]-[54] 

                        1.[0.400  ]                                                                                              Sub [35]-[36 

                        2.[1.490  ]                                                                                      ABS      [-3.000]-[3.000]

                        3.[3.480  ]                                                                                      Lin.    [0 ] EPLmt[2.000 ]

                        4.[8.940  ]                                                                                                             

                        5.[12.920  ]                                                                                                             

                        6.[17.880  ]                                                                                      Factor: Blank Cor.[1      ]

                                                                                                                                                 

                                                                                                                                                 

                        Method-                                                                                              ProZone               

                        Type  [END]                                                                                         Read  1[33]-[36] 

                        Color [600]-[]                                                                                                    2[43]-[46] 

                                                                                                                                                 3[49]-[52] 

                                                                                                                                      Limit   2[83  ]-[High ]

                        N-Range Serum                                                                                               3[82  ]-[High ]

                        M  [    ]-[          ]                                                                                    delta OD[0.039  ] 

                        F  [    ]-[          ] 

                       

                      I would like an outcome such as this in Modeler - I will try and supply the pdf version of this so you can have see what my report looks like.

                       

                      CONCENTRATION

                      33 46

                      43 46

                      49 52

                      82

                      82

                      0.037

                       

                      Thanks and best regards,

                      Ravi                                                                                         

                        • Re: Concatenate two columns
                          Chris Porthouse

                          If you could provide the PDF version you are attempting to model with would be helpful.  I think no matter what approach you use, you will still need to capture the values first and then use another formula field to remove the square brackets.  Another approach would be to capture each value separately into their own columns (possibly using regex traps).

                          • Re: Concatenate two columns
                            Austin Perkins

                            Ravi,

                             

                            Thanks for sending over the PDF example. I've forwarded you a copy of the template I created. I ended up selecting rows 16-21 and hitting "Replace Sample Text" with those rows. I used the following syntax for a regular expression trap in order to select the row with Read 1[33]-[36]: .\d\[(\d+)\]\s*\-?\s*\[(\d*)

                             

                            Chris has much more experience with the syntax and could certainly offer a cleaner more efficient trap but this worked for the sample file you provided. All amounts were imported using a detail template so all amounts are on the same row. I then created 3 calculated fields to concatenate read 1, 2, and 3 amounts into a single field for each read. The resulting output looks as follows:

                             

                            Read 1      Read 2      Read 3    Limit 2      Limit 3    Delta OD

                            33 36         43 46          49 52         83           82            0.039

                             

                            Let me know if you have any questions.

                             

                            Austin