return text value from cell w/formula

NicholasP

Active Member
Joined
Nov 18, 2006
Messages
289
Hi --

I have a cell that reads "=XX"....this cell is part of an output from another program (Bloomberg). The problem is that I want to capture cell value ("=XX") as text. However, excel automatically keys in on the "=" and tries to calculate this....I have been unsuccessful in trying to coax the "=XX"....I know that putting a single ' in front of the "=" would solve the problem, but I have a lot of these cells...I have tried VALUE, TEXT, LEFT...I'm at a loss on this one...

Any suggestions would be greatly appreciated.

Thanks
Nick
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Are you saying you want to input data like that.....or do something with the existing entries?

If you just want to enter =XX as text then you can pre-format the column (or any range) as text, That needs to be done before you enter the data
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
Hi --

I have a cell that reads "=XX"....this cell is part of an output from another program (Bloomberg). The problem is that I want to capture cell value ("=XX") as text. However, excel automatically keys in on the "=" and tries to calculate this....I have been unsuccessful in trying to coax the "=XX"....I know that putting a single ' in front of the "=" would solve the problem, but I have a lot of these cells...I have tried VALUE, TEXT, LEFT...I'm at a loss on this one...

Any suggestions would be greatly appreciated.

Thanks
Nick
It's not clear what you want to do. You say you want to "capture" the cell value as text. Ok, what does capture mean?
 

NicholasP

Active Member
Joined
Nov 18, 2006
Messages
289
Sorry for not being clearer...

Example:

The value in A1 will be "=XX". Excel assumes this is an unknown UDF or incorrect formula, so it attempts to calculate it and returns an error.

I want to be able to write a formula that would return "=XX". Currently the only way I can get this value is to manually insert a single apostrophe in front of "=XX" (thus, to get what I want manually, the cell value will read " '=XX" -- I input the space for emphasis).

The reason I want to do this is because I'm trying to preserve the comments of one of the users of the Bloomberg...He inputs this comment to signify that something is equal to XX, thus "=XX"...I imagine the formula would be something like "=UNKNOWN(A1)" and the result would be "=XX"...

If I'm still being unclear, please let me know and I'll attempt to restate my condition again.

Thanks
Nick
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
Sorry for not being clearer...

Example:

The value in A1 will be "=XX". Excel assumes this is an unknown UDF or incorrect formula, so it attempts to calculate it and returns an error.

I want to be able to write a formula that would return "=XX". Currently the only way I can get this value is to manually insert a single apostrophe in front of "=XX" (thus, to get what I want manually, the cell value will read " '=XX" -- I input the space for emphasis).

The reason I want to do this is because I'm trying to preserve the comments of one of the users of the Bloomberg...He inputs this comment to signify that something is equal to XX, thus "=XX"...I imagine the formula would be something like "=UNKNOWN(A1)" and the result would be "=XX"...

If I'm still being unclear, please let me know and I'll attempt to restate my condition again.

Thanks
Nick
OK, I think I understand...

If A1 contains =XX then Excel returns the #NAME? error.

If you have a whole range of cells like that I was able to clear the error by doing an Edit>Replace operation.

Find what: =
Replace with: '=
Replace All

The cell will display =XX but the formula bar will display '=XX.
 

NicholasP

Active Member
Joined
Nov 18, 2006
Messages
289
I was looking for a formula based answer, but in reality this is part of a macro. I have used find/replace on numerous occasions and don't know why I didn't think of that....thanks!

Nick
 

Forum statistics

Threads
1,141,019
Messages
5,703,763
Members
421,315
Latest member
awaisnazir139

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top