|
Occasionally Workbooks users want to export people data in order to merge it into a hard copy mailshot. In these circumstances there is a requirement to show the different lines of a multi-line address field in separate columns in a report so that it can be exported easily to Excel. This is possible to do by using a substring formula in your Workbooks report. The same technique can be used to split the contents of other multi-line text fields into different columns but for this example the focus is on the street address.
| 1. |
Create a report based on People.
|
| 2. |
Add a calculated column for the first line of the address using the following formula:
|
| |
TRIM(REPLACE(RIGHT(SUBSTRING_INDEX( main_street_address, '\n', 1), LENGTH(SUBSTRING_INDEX( main_street_address, '\n', 1))- LENGTH(SUBSTRING_INDEX( main_street_address, '\n', 0))), ',' ,''))
|
| 3. |
Add a calculated column for the second line of the address using the following formula:
|
| |
TRIM(REPLACE(RIGHT(SUBSTRING_INDEX( main_street_address, '\n', 2), LENGTH(SUBSTRING_INDEX( main_street_address, '\n', 2))- LENGTH(SUBSTRING_INDEX( main_street_address, '\n', 1))-1), ',' ,''))
|
| 4. |
Continue adding calculated columns until you've accommodated the maximum number of rows in any of your address fields (typically 4 or 5 but each database can be different). For each subsequent row, use the formula shown for the second line but increment the number highlighted in yellow by one each time. So, for example, the formula for the third line of the address would be:
|
| |
TRIM(REPLACE(RIGHT(SUBSTRING_INDEX( main_street_address, '\n', 3), LENGTH(SUBSTRING_INDEX( main_street_address, '\n', 3))- LENGTH(SUBSTRING_INDEX( main_street_address, '\n', 2))-1), ',' ,'')) |
|
