MS Excel: convert formula into text Thread poster: Samuel Murray
|
|---|
Samuel Murray 
Netherlands
Local time: 13:21
Member (2006)
English to Afrikaans
+ ...
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/p... See more 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 ▲ Collapse
| | | | | 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
| | | | Samuel Murray
Netherlands
Local time: 13:21
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.
| | | |
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
| | |
|
|
|
Alp Berker
United States
Local time: 07:21
Turkish to English
+ ...
In memoriam | 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.
| | | | gumuruhsspj
Local time: 18:21
English to Indonesian
+ ...
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.
| | | | Samuel Murray
Netherlands
Local time: 13:21
Member (2006)
English to Afrikaans
+ ...
TOPIC STARTER
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.
| | | | 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 | Protemos translation business management system | Create your account in minutes, and start working! 3-month trial for agencies, and free for freelancers!
The system lets you keep client/vendor database, with contacts and rates, manage projects and assign jobs to vendors, issue invoices, track payments, store and manage project files, generate business reports on turnover profit per client/manager etc.
More info » |
| | Trados Business Manager Lite | Create customer quotes and invoices from within Trados Studio
Trados Business Manager Lite helps to simplify and speed up some of the daily tasks, such as invoicing and reporting, associated with running your freelance translation business.
More info » |
|
| | | | X Sign in to your ProZ.com account... | | | | | |
Login to reply/comment 
