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?

smilingfrog
  • 251
  • You could write a short user-defined function as suggested at http://www.mrexcel.com/forum/excel-questions/73743-convert-text-formula.html. 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 02 '15 at 21:29

3 Answers3

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.

smilingfrog
  • 251
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
Dave
  • 1,204
White Knight
  • 11
  • In step 3, does the & indicate a formula? The regex seems to indicate that you are replacing the first character with an ampersand. – smilingfrog Jan 18 '21 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.

user695827
  • 11