16

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

Improve this question

2 Answers 2

Reset to default
25

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>")
Improve this answer
3
  • Thanks for the answer, this is the exact formula I tried first. unfortunately the " acts literally. I couldn't find a method to escape the character, so tried to use CHAR(22) based on my local character set that also... did not work. Other than swapping the " for ' im still at a loss on this one.
    – David Barker
    Commented Mar 28, 2012 at 15:59
  • thank you! The double quote enclosed by double quotes didnt work but changed the charset to use ASCII and CHAR(34) worked.
    – David Barker
    Commented Mar 30, 2012 at 12:33
  • Thanks! The CHAR(34) did the trick. The repeated quotes (tried 3, 4, 5 of them) worked only until I closed and re-opened the file - then the quotes had turned into a 0.
    – Jānis Elmeris
    Commented Apr 18, 2016 at 7:07
5

suppose 'AF' was in column A1 and 'Afghanistan' was in column C1, then this would produce the desired result

    ="<option value='"&A1&"'>"&C1&"</option>"

That code would give you this output

    <option value='AF'>Afghanistan</option>
Improve this answer
1
  • This answer works also great with gnumeric (couldn't make the other answer working).
    – Skippy le Grand Gourou
    Commented Sep 6, 2016 at 9:50

Not the answer you're looking for? Browse other questions tagged or ask your own question.