Retrieving formula values

I often have to concatenate() several columns in a spreadsheet that I am going to import into another program (like “first name”, “last name” in the original will become “name” in the spreadsheet for import.)

Obviously, when I import this spreadsheet, I want to import the values, not the formula…

Currently what I do is:

*Assume that column A is “First Name” and column B is “Last Name”

  1. Create a new column C (name1)

  2. Insert formula “=concatenate(A2,” “,B2)”

  3. Copy formula through the column

  4. Create a new column D (name2)

  5. Select all data in column C (name1) and copy to clipboard

  6. Paste-special to column D (name2) to move just the values

  7. Delete columns A, B and C

My question is, is there a way to perform steps 4-6 programmatically (e.g. change all cells in column C to their calculated value instead of formula?)


You can easily write a macro for it but then you can even do the concatenation in the macro.

You are even free which language to use (StarBasic, Java, C++, C#, Python,…)

For more information see

Perhaps some kind of macro reference would be helpful here… or some example code?

(even a short macro example would be good to have here before we try to resolve this question)