Spreadsheet Concatenation
Use concatenation to combine information in different cells in a spreadsheet.
This is used to either add leading numbers to cell numbers, i.e. the country code or leading zero, or to make a personalized message for Multi-Message uploads. This will require a spreadsheet program - the directions are for Excel but can be applied to other spreadsheets such as OpenOffice.org.
Example 1:
Adding country codes or leading zero to cell numbers.
Open the Excel spreadsheet that contains the incomplete cell number. This example assumes that Cell A contains the cell number and Cell B and C contain first name and surname.
| Cell A | Cell B | Cell C | Cell D |
| 821234567 | David | Smith | Cape Town |
| 821234568 | Susan | Smith | Cape Town |
| 821234569 | Kevin | Smith | Stellenbosch |
Insert 3 new columns before column A.
Leave the new column A blank. In column B type the formula. In column C enter the country code or leading zero.
The formula you will use is: =concatenate(c1,d1)
The formula will need to contain the correct row number and will display the combined information of the cells.
Use the drag or copy feature to add this formula to each row.
| Cell A | Cell B | Cell C | Cell D |
| =concatenate(c1,d1) | 27 | 821234567 | |
| =concatenate(c2,d2) | 27 | 821234568 | |
| =concatenate(c3,d3) | 27 | 821234569 |
Column B will show the combined information from columns C and D.
Then copy column B.
Select column A.
Select 'Paste Special'.
Select 'Value' to paste.
This will then paste the text and not the formula.
Delete columns B, C and D
You will be left with cell number in A and First name and Surname in B and C.
Save as: 'Text (Tab-delimited) File'
| Cell A | Cell B | Cell C | Cell D |
| 27821234567 | David | Smith | Cape Town |
| 27821234568 | Susan | Smith | Cape Town |
| 27821234569 | Kevin | Smith | Stellenbosch |
Example 2:
Create a personalized Multi-Message Text (Tab delimited) File
Open the Excel spreadsheet that contains the cell number and name. This example assumes that column A contains the cell number and column b contains the name.
| Cell A | Cell B | Cell C | Cell D |
| 27821234567 | David | ||
| 27821234568 | Susan | ||
| 27821234569 | Kevin |
In column C type a space and the standard message. (The leading space will be needed for concatenation.). In column D enter the formula.
The formula you will use is: =concatenate(b1,c1)
The formula will need to contain the correct row number and will display the combined information of the cells.
Use the drag or copy feature to add this formula to each row.
| Cell B | Cell C | Cell D | Cell E |
| David | send me an SMS | =concatenate(b1,c1) | |
| Susan | send me an SMS | =concatenate(b2,c2) | |
| Kevin | send me an SMS | =concatenate(b3,c3) |
Column D will show the combined information from columns C and D.
Then copy column D.
Select column E.
Select 'Paste Special'.
Select 'Value' to paste.
This will then paste the text and not the formula.
Delete columns B, C and D
You will be left with cell number in A and personalized message in column B.
| Cell A | Cell B | Cell C | Cell D |
| 27821234567 | David send me an SMS | ||
| 27821234568 | Susan send me an SMS | ||
| 27821234569 | Kevin send me an SMS |