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.
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:
2) Simply type out the two fields you are trying to concatenate with the plus "+" sign between them:
CONCENTRATIONRAW1 + CONCENTRATIONRAW2
Hope this helps
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;
Is this possible?
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.
"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."
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.
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 Y=[1.000]X+[0.000]
Mon. Span[3.000] ZeroP[1
Type[Logit 2 ] Read Main-
1.[0.400 ] Sub -[36
2.[1.490 ] ABS [-3.000]-[3.000]
3.[3.480 ] Lin. [0 ] EPLmt[2.000 ]
6.[17.880 ] Factor: Blank Cor.[1 ]
Type [END] Read 1-
Color - 2-
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.
Thanks and best regards,
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).
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-: .\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.