This may be more general but here goes...

I've written an XLL extension which merges cells together (think the exact
opposite of "text to columns"). So what should happens is

Merge cells 1,2,3 to X thus...
Cell1: 1234
Cell2: ABCD
Cell3: 5678
== CellX: 1234ABCD5678

Works a treat but there's more. Like the inverse of text to columns, I can
add delimiters (in case there are odd spaces etc) and also separators. So
let's repeat the above but use double-quotes as delimiters and commas as
separators...

==CellW: "1234","ABCD","5678"

It's still working. But if I use SINGLE quotes as delimiters, what Excel
displays is

==CellZ: 1234','ABCE','5678' !! Note the MISSING leading single
quote !!

Now there are some real oddities here...

1. I guess Excel is using the first quote to mean "display as text" but...
2. If I manually "edit" CellZ, Excel shows the leading (missing)
single-quote in the edit box.
3. If I create a similar cell, with the leading single-quote, manually and
use a formula to ask "are they the same", Excel claims they are not.
4. If I repeat #3 but remove the leading quote from my "manually created"
cell, it STILL doesn't match the value my extension created.

So, any ideas? It's not a biggy but I'm a real pernickety person and I'd
like to know what I should, or should not, be doing to fix this.

Paul DS.

On Mon, 16 Apr 2012 09:18:38 +0100, "Paul D Smith" wrote:

This may be more general but here goes...

I've written an XLL extension which merges cells together (think the exact
opposite of "text to columns"). So what should happens is

Merge cells 1,2,3 to X thus...
Cell1: 1234
Cell2: ABCD
Cell3: 5678
== CellX: 1234ABCD5678

Works a treat but there's more. Like the inverse of text to columns, I can
add delimiters (in case there are odd spaces etc) and also separators. So
let's repeat the above but use double-quotes as delimiters and commas as
separators...

==CellW: "1234","ABCD","5678"

It's still working. But if I use SINGLE quotes as delimiters, what Excel
displays is

==CellZ: 1234','ABCE','5678' !! Note the MISSING leading single
quote !!

Now there are some real oddities here...

1. I guess Excel is using the first quote to mean "display as text" but...
2. If I manually "edit" CellZ, Excel shows the leading (missing)
single-quote in the edit box.
3. If I create a similar cell, with the leading single-quote, manually and
use a formula to ask "are they the same", Excel claims they are not.
4. If I repeat #3 but remove the leading quote from my "manually created"
cell, it STILL doesn't match the value my extension created.

So, any ideas? It's not a biggy but I'm a real pernickety person and I'd
like to know what I should, or should not, be doing to fix this.

Paul DS.

I have no experience with XLL's. But doing this in VBA, I would have to output two single quotes at the beginning of the string. So I would test to see if the first delimiter was a single quote, and, if it is, add another. The first tells Excel this is a text string; the second is a part of the text string.

I have no experience with XLL's. But doing this in VBA, I would have to
output two single quotes at the beginning of the string. So I would test
to see if the first delimiter was a single quote, and, if it is, add
another. The first tells Excel this is a text string; the second is a
part of the text string.

Thanks - I'll give it a whirl.
Paul DS.