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'p>

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