Hi All

I have a puzzling issue with a worksheet I'm working on.

I'm supplying catalogue numbers to a list of items by concatenating 3
three columns. Typically , this is correctly giving numbers such as

12234-12-56
13498-7-89
85787-cb-78

This is working fine for the vast majority of items but for a few ,
which steadfastly refuse to keep the format the concatenation demands.

For example , a cell that should read 6534-12-10 is in fact reading
1692879. Others show a date , like 10/12/6541

This is only happening to about 15 cells in a list of more than 20,000.
I've tried changing formats to no avail.

Does anyone have any ideas why this should be happening? I'm not asking
Excel to interpret anything in the cell , just to represent the
concatenation accurately.


Grateful for any advice.


Best Wishes

This is only happening to about 15 cells in a list of more than 20,000.
I've tried changing formats to no avail.

Formatting does not change underlying values. And underlying values are the
data evaluated by your concat formula (or any formula)

My guess is a little upstream, that those "errant" data have somehow been
changed by Excel into real dates in the import process. In step 3 of the
Data Text to Cols import wizard, check "Text" under col data format for
the 1st col involved in the concat.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,400 Files:362 Subscribers:58
xdemechanik
---

On Fri, 3 Oct 2008 00:18:03 +0100, Colin Hayes
wrote:



Hi All

I have a puzzling issue with a worksheet I'm working on.

I'm supplying catalogue numbers to a list of items by concatenating 3
three columns. Typically , this is correctly giving numbers such as

12234-12-56
13498-7-89
85787-cb-78

This is working fine for the vast majority of items but for a few ,
which steadfastly refuse to keep the format the concatenation demands.

For example , a cell that should read 6534-12-10 is in fact reading
1692879. Others show a date , like 10/12/6541

This is only happening to about 15 cells in a list of more than 20,000.
I've tried changing formats to no avail.

Does anyone have any ideas why this should be happening? I'm not asking
Excel to interpret anything in the cell , just to represent the
concatenation accurately.


Grateful for any advice.


Best Wishes



1692879, when formatted as a date, is, in fact, 12/10/6534 (10-Dec-6534)

We need to know more about what you are doing.

How are you executing the concatenation?

What is the contents of the precedent cells for the values that get transformed
into dates?

Are these values entered as constants, or are they the results of formulas? If
the latter, what are their precedents?

What is the format of the cells at the time you first execute the
concatenation?
--ron

Colin Hayes wrote...
....
12234-12-56
13498-7-89
85787-cb-78

These should be text.

This is working fine for the vast majority of items but for a few ,
which steadfastly refuse to keep the format the concatenation demands.

For example , a cell that should read 6534-12-10 is in fact reading
1692879. Others show a date , like 10/12/6541
....

Excel is treating these as dates. Excel may even have converted them
into dates. The robust, though redundant way to handle this is to
assume ALL these cells have been converted to dates. So instead of

=B99&C99&D99

where C99 should the catelog number, try

=B99&IF(COUNT(C99),TEXT(C99,"yyyy-m-d"),C99)&D99

Use this for every record (hence the redundancy), and there should be
no problems.