How do I concatenate cells and add extra text?

openoffice-calc
David Barker picture David Barker · Mar 28, 2012 · Viewed 40.2k times · Source

I'm very new to Calc but a relative veteran with Excel. Unfortunately I don't have the latter available to me. I'm attempting to create a new cell inline with the data I need to use like the below

AF    Afghanistan
AL    Albania
DZ    Algeria

with an output in Column C like this

<option value="AF">Afghanistan</option>

I've tried to use the CONCATENATE function to no avail. Could someone point me in the right direction on how to achieve this in OpenOffice Calc (Version 3).

Thanks

Answer

tohuwawohu picture tohuwawohu · Mar 28, 2012

I suppose it's a problem of escaping the quotes, since they delimit the "extra strings", too. Anyway, it should work with CONCATENATE, using this formula:

=CONCATENATE("<option value=""";A1;""">";B1;"</option>")

EDIT:

Sorry, every time messing up argument separators (with german l11n, semicolons instead of commata are used...) With an english (US) localisation, you need this version:

=CONCATENATE("<option value=""",A1,""">",B1,"</option>")

If doubling the qoutes around the first cell reference doesn't work, try to replace it with CHAR(34) (the decimal ASCII code for double quotes is 34, while 22 would be the hex value):

=CONCATENATE("<option value=",CHAR(34),A1,CHAR(34),">",B1,"</option>")

Related questions

How to quote a quote with the CONCATENATE function in OOCalc

In OOCalc I want to use the CONCATENATE function to add quotes to each string in column A. So in cell B1 I want to do: =CONCATENATE("\"",A1,"\"") OOCalc does not like this, or without the escaping backslash. Does anyone …

Read More
How to add number of days to a date, consider only business days (i.e. ignore weekends)?

I'm trying to create a formula to calculate the n-th Business Date (only Monday to Friday are business days). For simplicity's sake, holidays are not important; only weekends should be ignored. For example: +------------------------------------------------------------------ | A B C +------------------------------------------------------------------ 1 | Starting Date …

Read More
Join two spreadsheets on a common column in Excel or OpenOffice

I have two CSV files with a common column and I want to "Join" the tables together on the common column. For example: Join 'A' with 'B' equals 'Result'. If a one table has a key value that does not …

Read More