5

I have a spreadsheet that generates formulae as text ex.

=CONCATENATE("+B",ROW(),"*",ROUND(G5,2))    

output:

 +B5*1464.41

I have a column of this type of text that I need to convert into individual formulae. Right now, I copy the text to a new column, and then push the '=' button for each row. Is there a way to automate this? or do it in bulk?

1
  • You could write a short user-defined function as suggested at mrexcel.com/forum/excel-questions/…. Unfortunately LibreOffice Basic doesn't include the Evaluate() function, so you'd have to figure out how to call a function in another language (such as JavaScript or Python).
    – Lyrl
    Dec 2, 2015 at 21:29

3 Answers 3

1

Answering my own question, the solution is rather obvious.

Highlight the text in question. Use Find to replace the leading '+' with a '='

The cells will recalculate as a formula instead of text.

1

Try using a regular expression with "paste special". All cells have a leading - generally invisible - character defining the cell type.

So:

  1. Select all the cells with the textual formula and press CTRL+H.
  2. in the FIND box type ^.. (You may have to type the ^ symbol twice to make it appear - then backspace to delete one of them and type the period .)
  3. In the REPLACE box type &. Make sure Regular Expressions box is checked
  4. Select FIND ALL which should highlight all your selected cells
  5. Select REPLACE ALL
1
  • In step 3, does the & indicate a formula? The regex seems to indicate that you are replacing the first character with an ampersand. Jan 18, 2021 at 17:15
0

whenever i have a considerable number of text lines that i need to convert to executable code, i copy/paste the text lines from the spreadsheet into an ordinary, blank libreoffice writer document and then copy/paste them from the document into a libreoffice calc spreadsheet.

the pasting into the writer document must be paste special/unformatted. then the pasting from the writer document into a spreadsheet must be ordinary paste - not unformatted.

i've had 20,000 lines of code that had been generated as text execute immediately using this method.

edit - i really like your method, frog. what i've been doing is limited to columns of code, while your method works equally well on rows of code.

1
  • This does not appear to answer the author's question.
    – Ramhound
    Feb 9, 2017 at 21:38

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .