text values ignored when first cell under header has numeric value

[Glen]

fIn one of those the-broken-appliance-always-works-for-the-repairman moments,
Word has started behaving itself since I started writing this post. However,
I'll finish it anyway in the hope someone can shed a little light for next
time...

Hi,

I have a mail merge document in Word03. Data source is a spreadsheet in
Excel03.

The columns with address details are Person1, Person2, Address1,
Address2...Address4. The values for these records come from a vlookup() which
finds the details from a larger address list on a different sheet in the same
workbook. The spreadsheet side of things works fine.

However, in the merge document the way the Address4 field is responding is
doing my head in.

Symptoms (of document, not head) are:
Initially, for all merged records Adress4 returns a 0 (the value returned by
the vlookup when the main list has a blank cell), even for the records where
the vlookup has returned a text value.

Experimenting by overwriting the formula in Address4 with other values has
produced interesting results:
*records changed to numeric values other than zero are picked up OK but text
values still ignored.
*changing format of column from general to text made no difference
*changing value of first record in Address4 from numeric to text suddenly
results in the other records with text values now being merged correctly in
Word.

Following the last discovery, I have nested the vlookup in Address4 inside a
concatenate(), and it all works fine as all values are returned as text.

But why does word decide that all values in a column will be treated as
numeric if the first value is numeric? Is there somewhere to turn this off?
And why did this only affect Address4 when Address3 also had a zero in the
first record?

Thanks in advance,

Glen

 

[Peter Jamieson]

There's a lengthy explanation

http://tips.pjmsn.me.uk/t0003.htm

As long as you don't have non-ANSI characters in your data, the DDE option
is probably the way to go, nasty though it is.

Peter Jamieson

 

[Glen]

Thanks for that Peter. At least now I can plan to avoid the chaos. The last
mailout (600 letters) was already to post before I noticed the random address
issues.

Cheers,

Glen