Basic Reporting Part V FAQ: How Do I Display the Values of Two or More Cells In One Cell
- Concatenate is a function/formula that can be used in report writing to put the values from two or more fields together in one cell.
- Concatenate can be used on text – including, words, numbers, and symbols – as well as field values.
- For example, if the address fields on a report are in separate columns and you wish them to be altogether in one column to save space (make the report not so long/wide), then you can use concatenate to put all those fields in the same cell like this:
- Or, perhaps you are writing a report that needs to show each contact’s salutation, but the data is not updated/filled in in your database and you don’t have time to correct the data right now. You can use concatenate to put the value from the Prefix Field and Last Name fields together in the same cell to make a formal salutation (remember to also concatenate in a space between the two fields so the two parts don’t run together into one word) [if your contact records are missing a prefix field, you could get super fancy and combine an “if/then” statement with concatenate so that the report will use “Mr.” plus the contact’s last name if the gender equals male and “Ms.” plus the contact’s last name if the gender equals female].
- Or, perhaps you have a subtotal per person on your report and you wish to add text to the subtotal consisting of the contact’s name and the word “subtotal” to make it clearer what each sub-total is. You can use concatenate to put the value from the contact name field plus an apostrophe and an "S" plus the word “subtotal” together in one field, like this:
- To get started, click into the cell where you wish to put a concatenated value and then click the Formula/Function editor button (fx) in the Cell content toolbar.
- Next, click the drop down next to String. Find Concatenate in the drop down and either double click it or click and drag it to add it to the formula editor area.
- The syntax for concatenate is to separate all values you wish to put together by commas OR by ampersands (&) like this: Concatenate({field name #1}, {field name #2}). Text (words, numbers, symbols, SPACES, etc.) needs to be enclosed by quotation marks: Concatenate({First.Name},“’s”, “ “, “Sub-Total”)
- There is no limit to the number of items you can concatenate together. Just remember to separate each distinct item with commas.
Comments
0 comments
Article is closed for comments.