Strange blanks queer date strings [resolved]

noranthon · Super User Joined: 07 Jul 2005 Posts: 3323

This problem may be related to the thread Does 'find & replace' actually work at all in OOo?.

I have a sheet with data copied from an online bank account record. Recently I have taken to pasting the data as unformatted text, which produces a better result but previously I transferred the data by a simple paste and just put up with the anomalies - until today when I was overtaken by an urge to regularise the entries. Fortunately, that does not happen often.

The following is a sample of dates:
05 OCT 2005
06 OCT 2005
07 OCT 2005
14 OCT 2005
19 OCT 2005
19 OCT 2005
19 OCT 2005
20 OCT 2005

Some dates have following spaces. Others do not. That is another story. None of the recommended methods I've seen for disposing of trailing spaces worked. I had the same problem with numerical (i.e. currency) entries.

The only ways I could find of converting the dates to the desired format (dd/mm/yyyy) were (1) by re-entering the dates or (2) by searching for one date at a time and replacing it. In cases where a space followed the date I had to include the space in the searched text.

The search string .*OCT.* certainly found all such strings but replacing with .*/10/.* resulted in literally replacing with exactly that. Replacing with &/10/& resulted in no change to the cell contents.

DATEVALUE did not work, nor did VALUE. The error returned was 502 (invalid argument). Incorporating TEXT and/or LEFT made no difference. Adding the displayed format to the document as a date format made no difference.

The strings concerned displayed in exactly the same way regardless of formatting.

I tried inserting a leading apostrophe. It disappeared from view. Inserting two leading apostrophes resulted in one being visible in the input line.

It seems to me that Calc should have been able to carry out a wholesale change of the date strings. Any comments?

EDIT: altered title to reflect the outcome.
_________________
search forum by month

Villeroy · Posted: Wed Jun 14, 2006 3:19 am Post subject:

I can't reproduce this. It works perfectly with my OOo2.0.2 on Linux.
Pasting your example dates to A1:A8 gives strings with trailing spaces.
=DATEVALUE(A1) gives this result, formatted as default date (en_GB: DD/MM/YY)
05/10/05
06/10/05
07/10/05
14/10/05
19/10/05
19/10/05
19/10/05
20/10/05
Removal of trailing spaces works with regex " +$" (at least one space at end of string). Replace-box is empty.
The replace converts the strings to numbers, formatted like the date values above.
_________________
XUbuntu 9.04, OOo 3.1.1(Sun), Sun Java 1.5.0_06

noranthon · Super User Joined: 07 Jul 2005 Posts: 3323

Thanks, Villeroy. Perhaps I should have pasted all my dates here, then copied and pasted them into the spreadsheet. Copying and pasting from my previous post got me the dates in the default format. Pasting from the original copy got me the old problem.

I've pasted into a new file, removed the trailing spaces (manually) and uploaded it:
http://www.mytempdir.com/738867

You will see that DATEVALUE does not work.

(Incidentally, I tried searching for " +$" in the old file - it does not work there.)
_________________
search forum by month

Villeroy · Posted: Wed Jun 14, 2006 4:28 am Post subject:

=CODE(MID(A1;3;1))
returnes 0. OCT is in between null-chars.

Replace regex \x0000 with " " does not work with null-chars, nor does REPLACE(A1;CHAR(0);" ").
Copy a null-char from the formula
Paste into the search-box and replace with " ".
_________________
XUbuntu 9.04, OOo 3.1.1(Sun), Sun Java 1.5.0_06

noranthon · Super User Joined: 07 Jul 2005 Posts: 3323

Copying the apparent space between the characters, searching for it and replacing it with " " worked a treat. Thanks very much.

When I used the CODE formula, however, I got 160.

Lesson Number (I've lost count): " " is not always " ". Shocked

_________________
search forum by month

Villeroy · Posted: Wed Jun 14, 2006 4:49 am Post subject:

noranthon · Super User Joined: 07 Jul 2005 Posts: 3323

Thanks again, Villeroy. That regex thing. One day I'll have to tackle it properly. A recent post from JohnV on another thread mentions using the hex number in Basic. Perhaps the same is possible in regex?
_________________
search forum by month

Villeroy · Posted: Wed Jun 14, 2006 5:41 am Post subject:

Found my way for replacing all kinds of spaces with a "real space" (ascii 32):
Find Regex: [:space:]+
find any kind of space, optionally followed by more spaces
Replace with: single space
The implementation of named character-classes is broken and will change in OOo3: http://www.openoffice.org/issues/show_bug.cgi?id=64368
Currently [:space:] won't match whereas [:space:]? matches a single space, which is contrary to the meaning of "?" (zero or one occurance of preceeding item).
_________________
XUbuntu 9.04, OOo 3.1.1(Sun), Sun Java 1.5.0_06