Ask Your Question
0

Detect special numbers on paste in v5

asked 2016-09-02 00:15:36 +0100

JD4x4 gravatar image

Now that the pop up window with language and "Detect special numbers.." is gone from v5 (in Windows anyway), how can I keep Calc from detecting dates when I paste as html?

And, what is the latest (Windows) version of Calc that still has this pop up window?

(imo, What's really needed is enable/disable as some sort global preference, as it also seems that Calc reformats dates when a text to column operation is performed.)

edit retag flag offensive close merge delete

Comments

What was you version?, I can't see the pop up window pasting as HTML, only pasting as unformatted text with 3.6 or 4.2.

m.a.riosv gravatar imagem.a.riosv ( 2016-09-02 10:47:50 +0100 )edit

Hmm.. you might be right that on the Windows versions the language/options pop up never came appeared. I know it does on Mint/Ubuntu v 4.4, and I just downgraded my Windows version from 5 to 4.4.7 (uninstalled/reinstalled) and I'm not getting it in 4.4.7. :-(

I don't see a function to convert a date to text either. Anyone aware of an add-in or a macro I could use?

JD4x4 gravatar imageJD4x4 ( 2016-09-02 15:56:21 +0100 )edit

The problem is that I have multiple rows of mixed strings with dates (in various text formats) plus other text that I import via html to get decent column sort, but then need to parse the cols using text to columns and thats mainly when I loose my dates because I finally concatenate various cells and a date reverts to a date number.

JD4x4 gravatar imageJD4x4 ( 2016-09-02 16:02:08 +0100 )edit

Have you tried pasting as unformatted text, so you can select what columns should be treated as text, or what type of date or number.

m.a.riosv gravatar imagem.a.riosv ( 2016-09-02 23:09:52 +0100 )edit

Yes. The problem is that pasting as unformatted produces rows for 3 web table columns, whereas pasting as html gives me the 3 columns. It just also reformats the date strings if I don't immediately redefine the imported fields as text. Doesn't seem to matter if I've formatted the range as text before pasting.

I suppose I just need to live with it and be extra careful.

JD4x4 gravatar imageJD4x4 ( 2016-09-03 03:20:42 +0100 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2016-09-03 03:09:40 +0100

mark_t gravatar image

Use the TEXT function to convert a date number to a formatted text string.

=TEXT(A13,"d-mmm-yyyy")
edit flag offensive delete link more

Comments

Thanks. That seems to work. It would be nice though if the Windows version acted like the Linux versions and gave me the opportunity to define the entire pasted contents as text before hand so I wouldn't have to jump through these hoops. Not all of the dates are recognized and reformatted so it plays hell with my concatenation formula, having to do "If" tests and such.

Guess I'll live with it and import on my linux box, everything else on my PC.

JD4x4 gravatar imageJD4x4 ( 2016-09-03 03:28:16 +0100 )edit

Don't use the TEXT function for this, apply a date number format instead.

erAck gravatar imageerAck ( 2016-09-03 09:50:21 +0100 )edit

I suggested the TEXT function as @JD4x4 had indicated he was concatenating cells. Assuming the date is only one part of the concatenation then the TEXT function would provide the formatted date as a text string that could then be concatenated with other text.

mark_t gravatar imagemark_t ( 2016-09-03 14:33:27 +0100 )edit

Ah well, that appears only when digesting the comments as well, not in the question. Ok.

erAck gravatar imageerAck ( 2016-09-03 15:14:30 +0100 )edit
0

answered 2016-09-03 09:52:39 +0100

erAck gravatar image

The Text Import dialog is not gone, but it's activated only if there are at least two rows of data. Which always was the case.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-09-02 00:15:36 +0100

Seen: 200 times

Last updated: Sep 03 '16