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 column A contains the cell number and column b and c contain first name and surname.

821234567	David	Smith
821234568	Susan	Smith
821234569	Kevin	Smith
821234560	Ellen	Smith

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.

27821234567	=concatenate(c1,d1)	27	821234567	David	Smith
	=concatenate(c2,d2)	27	821234568	Susan	Smith
	=concatenate(c3,d3)	27	821234569	Kevin	Smith
	=concatenate(c4,d4)	27	821234560	Ellen	Smith

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'

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.

27821234567	David
27821234568	Susan
27821234569	Kevin
27821234560	Ellen

In column C type a space and the standard message. (The leading space will be needed for concatenation.)
In coumn 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.

27821234567	David	send me an SMS	=concatenate(b1,c1)	David send me an SMS
27821234568	Susan	send me an SMS	=concatenate(b2,c2)
27821234569	Kevin	send me an SMS	=concatenate(b3,c3)
27821234560	Ellen	send me an SMS	=concatenate(b4,c4)

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.
SAVE AS 'Text (Tab-delimited) File'