MS Excel: convert formula into text
Thread poster: Samuel Murray

Samuel Murray  Identity Verified
Netherlands
Local time: 11:53
Member (2006)
English to Afrikaans
+ ...
Aug 23, 2011

G'day everyone

In MS Excel, I have a column A which contains text (e.g. A1 may contain "foo"), and I have a column B in which I put this formula:

=CONCATENATE("xxx",A1,"xxx")

so that B1 now contains "xxxfooxxx".

However, I want the actual text in cell B1 to be "xxxfooxxx" and not the formula. Is there a way (via another formula) in which I can conver the formula in the cell to the actual text that it currently displays? I can't use the copy/paste-special route because I work with multiple cells and lots of hidden rows, so pasting is not an option).

Any ideas?

Thanks
Samuel


Direct link Reply with quote
 

Antoní­n Otáhal
Local time: 11:53
Member (2005)
English to Czech
+ ...
copy & paste ayway Aug 23, 2011

Samuel Murray wrote:
However, I want the actual text in cell B1 to be "xxxfooxxx" and not the formula. Is there a way (via another formula) in which I can conver the formula in the cell to the actual text that it currently displays? I can't use the copy/paste-special route because I work with multiple cells and lots of hidden rows, so pasting is not an option).


Why not? You can copy column "B" and paste it to column "B" as values.

Antonin


Direct link Reply with quote
 

Samuel Murray  Identity Verified
Netherlands
Local time: 11:53
Member (2006)
English to Afrikaans
+ ...
TOPIC STARTER
No, pasting with hidden rows don't work Aug 23, 2011

Antoní­n Otáhal wrote:
You can copy column "B" and paste it to column "B" as values.


If you copy a single column from row 1 to 3, and row 2 is hidden, then only the two visible rows are copied, but when you then paste, Excel will paste the content into row 1 and 2 (not into row 1 and 3, which are the two visible rows). This is why "paste as values" isn't an option.


Direct link Reply with quote
 

Antoní­n Otáhal
Local time: 11:53
Member (2005)
English to Czech
+ ...
OIC Aug 23, 2011

I haven't tried to copy columns with hidden rows yet, so I did not know.

If you can use VBA then

For j = 1 to [number of rows]
Cells(j,2) = Cells(j,1).Value
Next j

might do the trick, but I am not sure about the hidden cells in this case either.

Antonin


Direct link Reply with quote
 

Alp Berker  Identity Verified
United States
Local time: 05:53
Turkish to English
+ ...
Record a macro and look at the VBA Code Aug 23, 2011

Hi Samuel,
Record your copying and pasting as a Macro and look at the resulting VBA Code of the Macro. Maybe that might help you get the VBA code you need to do this.


Direct link Reply with quote
 

gumuruhsspj
Local time: 16:53
English to Indonesian
+ ...
suggesting Aug 24, 2011

Antoní­n Otáhal wrote:

I haven't tried to copy columns with hidden rows yet, so I did not know.

If you can use VBA then

For j = 1 to [number of rows]
Cells(j,2) = Cells(j,1).Value
Next j

might do the trick, but I am not sure about the hidden cells in this case either.

Antonin


By using a computer programming skill is of course one of a solution for extracting the text out of xls file.
Anyway, to the author of this thread, i wanted to ask;
have you tried a freeware out there that could extract your xls file into txt? I think that could solve your problems, incase your xls file is containing lots of data.


Direct link Reply with quote
 

Samuel Murray  Identity Verified
Netherlands
Local time: 11:53
Member (2006)
English to Afrikaans
+ ...
TOPIC STARTER
@Gumu Aug 24, 2011

gumuruhsspj wrote:
Have you tried a freeware out there...


Such as?

...that could extract your xls file into txt?


Do you mean that I save the Excel file as CSV and then resave the translated version as Excel again? The problem is that when you do that, all formatting is lost, and besides, I don't think the CSV file contains information about which rows are hidden or should be hidden.

Remember, getting the translatable text *out* is not a problem, but it doesn't help me if I get the text out and there is no way to get the back back in again.


Direct link Reply with quote
 


To report site rules violations or get help, contact a site moderator:


You can also contact site staff by submitting a support request »

MS Excel: convert formula into text

Advanced search






PDF Translation - the Easy Way
TransPDF converts your PDFs to XLIFF ready for professional translation.

TransPDF converts your PDFs to XLIFF ready for professional translation. It also puts your translations back into the PDF to make new PDFs. Quicker and more accurate than hand-editing PDF. Includes free use of Infix PDF Editor with your translated PDFs.

More info »
Wordfast Pro
Translation Memory Software for Any Platform

Exclusive discount for ProZ.com users! Save over 13% when purchasing Wordfast Pro through ProZ.com. Wordfast is the world's #1 provider of platform-independent Translation Memory software. Consistently ranked the most user-friendly and highest value

More info »



Forums
  • All of ProZ.com
  • Term search
  • Jobs
  • Forums
  • Multiple search