return text value from cell w/formula

[NicholasP]

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

 

[barry houdini]

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]

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]

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]

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]

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