How to get what excel cell is showing?

1
votes

I have a strange question.

Screenshot:

sample data

From the screenshot you can see that the formula bar is showing just a single apostrophe. This is on all of the blue cells. These should be dates but all this data is coming from a excel plugin (Board)

From what I know Excel treats this cells differently i.e. everything after the apostrophe should be shown as text but here there is nothing after the apostrophe and the cells are still showing a values.

Is there a way with formula that I can get and compare this value with another cell in different worksheet?

EDIT: If there is a way to get it as a text, could it be than converted into date format number?

EDIT 2 - SOLUTION As per Mahesh and Jeeped comments - there was a newline character in the formula bar

2
excelexcel-formula
What value is the cell showing? '? – BruceWayne
Most probably there's a newline after the apostrophe, which is forcing the actual date to be shown below. Press Ctrl + Shift + U to expand the formula bar. You'll be able to see it. – user4066647
Open the formula bar vertically. I'm pretty sure you have a line feed then the date. e.g. '&CHAR(10)&23/10/2017 – user4039065
omg, never thought of that. Thanks guys, there was a new line in the formula bar – al1en

2 Answers

2
votes

If the date is preceded only by a new line and there are no other characters after the date, this will be much simpler:

=VALUE(REPLACE(A1,1,1,""))

This will give you the date value in general format (i.e., 43033), so you'll have to format it as a date.

Note: Using DATEVALUE instead of VALUE will give you the same result.

1
votes

You have not confirmed the vbLF characters in the cells but try this in an unused cell to retrieve a true date.

=datevalue(trim(mid(substitute(kn1, char(10), rept(char(32), len(kn1))), len(kn1), len(kn1))))

Assuming that you are on a DMY regional format that should get you a number like 43028. Format it as a date.