locked
How to get displayed text of a cell? RRS feed

  • Question

  • I want to link a cell to show the displayed value (after it's formatted) in another cell.  For example, open a new workbook, in cell B1 enter formula "=A1", the returned value is "0", I want it to return "".

    Ryan

    • Moved by Chenchen Li Wednesday, April 5, 2017 7:32 AM
    Tuesday, April 4, 2017 9:36 PM

Answers

  • Try...

    =IF(A1<>"",A1,"")
    Hope this helps!

    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    • Marked as answer by Ryan0827 Wednesday, April 5, 2017 2:54 PM
    Tuesday, April 4, 2017 11:33 PM
  • Simply format B1 the same as A1.

    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"


    Wednesday, April 5, 2017 2:57 AM

All replies

  • Try...

    =IF(A1<>"",A1,"")
    Hope this helps!

    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    • Marked as answer by Ryan0827 Wednesday, April 5, 2017 2:54 PM
    Tuesday, April 4, 2017 11:33 PM
  • That formula works for my example I provided, but doesn't work if the A1 cell format = "0000000" and the user types in "123".  A1 displays "0000123", but the formula returns "123".

    Perhaps I've been working too long on this issue I'm missing something easy, but I'm baffled trying to get the displayed value of a cell.

    Ryan

    Wednesday, April 5, 2017 1:48 AM
  • Simply format B1 the same as A1.

    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"


    Wednesday, April 5, 2017 2:57 AM
  • Hello Ryan,

    This forum is for development issues related to Excel Object Model, I would move this thread into Excel IT Pro Discussions forum, thanks for your understanding.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, April 5, 2017 7:32 AM