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”
-
Create a new column C (name1)
-
Insert formula “=concatenate(A2,” “,B2)”
-
Copy formula through the column
-
Create a new column D (name2)
-
Select all data in column C (name1) and copy to clipboard
-
Paste-special to column D (name2) to move just the values
-
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?)
Thanks