Hi,

I have tried both the concatenate function and the A1&B1, for example,
method.

In two separate workbooks, I have two different problems!

1. In one workbook, only the formula is displayed. As I enter it, it
refers to the cells referenced in colour, etc., as always, but as soon as I
hit Enter, only the formula is displayed, not the result.

2. In another workbook, the result is displayed, however, in many cells,
only the last 2 or 3 characters in the 2nd cell referenced is displayed.

I have never had these problems and have tried formatting in various ways,
looked up troubleshooting, but nothing seems to help.

Thanks!

--
Thanks!

Dee

"dee" wrote:
> I have tried both the concatenate function and the A1&B1, for example,
> method. In two separate workbooks, I have two different problems!

> 1. In one workbook, only the formula is displayed. As I enter it, it
> refers to the cells referenced in colour, etc., as always, but as soon as I
> hit Enter, only the formula is displayed, not the result.

One guess: The formula cell was (unknown to you, of course) earlier
pre-formatted as Text. Try formatting the cell as general or number, then
re-confirm the formula (eg click inside the formula bar, press ENTER again).
The formula must be re-confirmed after the formatting before it'll work.

> 2. In another workbook, the result is displayed, however, in many cells,
> only the last 2 or 3 characters in the 2nd cell referenced is displayed.

The concat result returned by the formula could be exceeding the cell's col
width. Either try widening the col or format the cell to wrap text (via:
Format > Cells > Alignment tab > check "Wrap text" > OK)

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Some further thoughts on your 2nd question
> > 2. In another workbook, the result is displayed, however, in many cells,
> > only the last 2 or 3 characters in the 2nd cell referenced is displayed.
>
> The concat result returned by the formula could be exceeding the cell's col
> width. Either try widening the col or format the cell to wrap text (via:
> Format > Cells > Alignment tab > check "Wrap text" > OK)

And instead of say, in C1: =A1&" "&B1,

Try in C1:
=TRIM(A1)&" "&SUBSTITUTE(TRIM(B1),CHAR(10),"")

(Not sure, but there could be some stray line breaks in col B)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Thank you! Both of your suggestions worked like a charm... I didn't need to
do the second more complicated step, as just changing the column width solved
the problem, which I should have figured out myself!

Had been up working for about 15 hours straight by that time, though!
--
Thanks!

Dee


"Max" wrote:

> Some further thoughts on your 2nd question
> > > 2. In another workbook, the result is displayed, however, in many cells,
> > > only the last 2 or 3 characters in the 2nd cell referenced is displayed.
> >
> > The concat result returned by the formula could be exceeding the cell's col
> > width. Either try widening the col or format the cell to wrap text (via:
> > Format > Cells > Alignment tab > check "Wrap text" > OK)
>
> And instead of say, in C1: =A1&" "&B1,
>
> Try in C1:
> =TRIM(A1)&" "&SUBSTITUTE(TRIM(B1),CHAR(10),"")
>
> (Not sure, but there could be some stray line breaks in col B)
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---

Glad it helped !
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"dee" wrote:
> Thank you! Both of your suggestions worked like a charm... I didn't need to
> do the second more complicated step, as just changing the column width solved
> the problem, which I should have figured out myself!
>
> Had been up working for about 15 hours straight by that time, though!
> --
> Thanks!
>
> Dee

Hi, secondary question to this problem. I am able to concatenate & insert
leading apostrophes into a column containing numeric & alphanumeric data.
However, when I try to link tables in Access, no matches are returned because
I believe that column is being read as 'ABC123 instead of ABC123. If I click
on each individual cell and hit enter, it works correctly. However, I have a
thousand cells and am not really interested in doing anything individually.
Is there another step I am missing? How can I make the apostrophes be read
merely as leading apostrophes?

Thanks,
Tamsyn Hartlen


"Max" wrote:

> "dee" wrote:
> > I have tried both the concatenate function and the A1&B1, for example,
> > method. In two separate workbooks, I have two different problems!
>
> > 1. In one workbook, only the formula is displayed. As I enter it, it
> > refers to the cells referenced in colour, etc., as always, but as soon as I
> > hit Enter, only the formula is displayed, not the result.
>
> One guess: The formula cell was (unknown to you, of course) earlier
> pre-formatted as Text. Try formatting the cell as general or number, then
> re-confirm the formula (eg click inside the formula bar, press ENTER again).
> The formula must be re-confirmed after the formatting before it'll work.
>
> > 2. In another workbook, the result is displayed, however, in many cells,
> > only the last 2 or 3 characters in the 2nd cell referenced is displayed.
>
> The concat result returned by the formula could be exceeding the cell's col
> width. Either try widening the col or format the cell to wrap text (via:
> Format > Cells > Alignment tab > check "Wrap text" > OK)
>
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---

Try this .. Enter the number: 1 into a blank cell, then copy that cell. Then
right-click on the col > Paste special > Check "Multiply" > OK. That should
help remove the leading apostrophes.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"uncreative" <(E-Mail Removed)> wrote in message
news:79A19FB6-1194-4C5B-A241-(E-Mail Removed)...
> Hi, secondary question to this problem. I am able to concatenate & insert
> leading apostrophes into a column containing numeric & alphanumeric data.
> However, when I try to link tables in Access, no matches are returned
> because
> I believe that column is being read as 'ABC123 instead of ABC123. If I
> click
> on each individual cell and hit enter, it works correctly. However, I
> have a
> thousand cells and am not really interested in doing anything
> individually.
> Is there another step I am missing? How can I make the apostrophes be read
> merely as leading apostrophes?
>
> Thanks,
> Tamsyn Hartlen

Hi,

That did not seem to work, unfortunately. Do you think I am having a
problem because I have both numeric and alphanumeric text? I want the
numeric cells to be treated as text, and in order to do that I need to insert
apostrophes. However, perhaps also inserting the apostrophes in the
alphanumeric cells is causing them to be read with the apostrophe, and
therefore the entire column is not being read.


"Max" wrote:

> Try this .. Enter the number: 1 into a blank cell, then copy that cell. Then
> right-click on the col > Paste special > Check "Multiply" > OK. That should
> help remove the leading apostrophes.
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> "uncreative" <(E-Mail Removed)> wrote in message
> news:79A19FB6-1194-4C5B-A241-(E-Mail Removed)...
> > Hi, secondary question to this problem. I am able to concatenate & insert
> > leading apostrophes into a column containing numeric & alphanumeric data.
> > However, when I try to link tables in Access, no matches are returned
> > because
> > I believe that column is being read as 'ABC123 instead of ABC123. If I
> > click
> > on each individual cell and hit enter, it works correctly. However, I
> > have a
> > thousand cells and am not really interested in doing anything
> > individually.
> > Is there another step I am missing? How can I make the apostrophes be read
> > merely as leading apostrophes?
> >
> > Thanks,
> > Tamsyn Hartlen
>
>
>

Not sure, but perhaps try this ..

Assuming source in A1 down

Put in B1:
=IF(A1="","",
IF(ISNUMBER(A1),TEXT(A1,"@"),A1))

Copy B1 down
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"uncreative" <(E-Mail Removed)> wrote in message
news:9A8893BF-D62C-40A4-BA41-(E-Mail Removed)...
> Hi,
>
> That did not seem to work, unfortunately. Do you think I am having a
> problem because I have both numeric and alphanumeric text? I want the
> numeric cells to be treated as text, and in order to do that I need to
> insert
> apostrophes. However, perhaps also inserting the apostrophes in the
> alphanumeric cells is causing them to be read with the apostrophe, and
> therefore the entire column is not being read.

Ahhh, so simple. That DID work this time, thanks for all your help!

"Max" wrote:

> Not sure, but perhaps try this ..
>
> Assuming source in A1 down
>
> Put in B1:
> =IF(A1="","",
> IF(ISNUMBER(A1),TEXT(A1,"@"),A1))
>
> Copy B1 down
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> "uncreative" <(E-Mail Removed)> wrote in message
> news:9A8893BF-D62C-40A4-BA41-(E-Mail Removed)...
> > Hi,
> >
> > That did not seem to work, unfortunately. Do you think I am having a
> > problem because I have both numeric and alphanumeric text? I want the
> > numeric cells to be treated as text, and in order to do that I need to
> > insert
> > apostrophes. However, perhaps also inserting the apostrophes in the
> > alphanumeric cells is causing them to be read with the apostrophe, and
> > therefore the entire column is not being read.
>
>
>