Cell value vs displayed text

[haibiz9]

Hello Excel masters,

I'm struggling to get a cell content to display correctly using macros. The cell content is =--7-31 and the displayed text is -24. How do I get it to display --7-31? The equal sign in front throws it off. I've tried all the popular string manipulation functions to no avail. Any help is greatly appreciated.

Other examples:

=--7-31 is currently displayed as -24 while I'd like Excel to show --7-31
=--3-29 is currently displayed as -26 while I'd like Excel to show --3-29
=--10-30 is currently displayed as -20 while I'd like Excel to show --10-30

 

[pgc01]

Hi

I don't understand why you have the equal sign in the cell. Why

=--7-31

if you don't want the equal?

You can use the equal sign, if you use a text constant, like

="--7-31"

but, as I said, I don't know why you are using the equal sign.

Another way, is to format the cell as Text and then enter:

--7-31

Again, no equal sign.

Please comment.

 

[Smitty]

Or use an apostrophe:

'--7-31

 

[haibiz9]

Hi pgc01, Thank you for your reply. I really appreciate it. The cell content with the equal sign is imported from another program so I have no control over it. My job is to use macros to format the cell content so that it looks correct. Please let me know if you know a trick to get this done. Thank you.

 

[haibiz9]

Hello Smitty, I have to write a macro to format the cell content. Handkeying is not an option for me. Please refer to my reply to pgc01 for more info. Thanks.

 

[haibiz9]

I've figured out a line of code to solve this problem

ActiveCell.Value = Right(CStr(ActiveCell.Formula), Len(CStr(ActiveCell.Formula)) - 1)

Thank you for your responses.