[Solved] Concatenate not combining text

[David12846]

Okay, I give up. I have 3 columns with data in all the cells in all the rows. All the columns are formatted as text.
For Example:

----A-----B-----C----D
1--(75---23---JB
2--(7a---35---JC
3--(xx---27---JD

I entered =CONCATENATE(A1;B1;C1) as a formula on the input line for Cell D1 and hit <ENTER>. The formula appears in D1. I expected to see (7523JB in the cell. Instead I got:

----A-----B-----C----D
1--(75---23---JB---=CONCATENATE(A1;B1;C1)
2--(7a---35---JC
3--(xx---27---JD


In Microsoft Excel, after typing the function in the input line and pressing <ENTER>, the function is processed and the concatenated text appears in cell D1. Then I drag the lower right corner of cell D1 downward and all the other rows are concatenated. Here's what I would get in Excel:

----A-----B-----C----D
1--(75---23---JB---(7523JB
2--(7a---35---JC---(7a35JC
3--(xx---27---JD---(xx27JD


HOW do I do this very simple thing in OpenOffice Calc? I've tried entering just the column letter in the formula. I've tried highlighting column D and entering a formula using column numbers (1 for the first column, 2 for the second column, etc. That works in Excel). I've even tried using A1&B1&C1 instead of A1;B1;C1.

I feel ashamed to say it, but I've been an Oracle DB and application programmer for 15+ years, but I'm lost here.

[acknak]

Are you still using OOo 3.0? If so, perhaps an upgrade will fix your problem: using OOo 3.3 (the current stable release), I get the same results you show for Excel.

On the other hand, if I try it with OOo 3.0.1, I also get the answers you show for Excel, so I'm not sure why it's not working for you.

[RoryOF]

Might the installation be set to display formulae not values? From the Help:

Code: Select all

Displaying Formulae or Values
If you want to display the formulae in the cells, for example in the form =SUM(A1:B5), proceed as follows:
1. Choose Tools - Options - OpenOffice.org Calc - View.
2. In the Display area mark the Formulae check box. Click OK.
If you want to view the calculation results instead of the formula, do not mark the Formulae check box.

Edit: Even simpler: What is Column D formatted to? Text? If so, then the formula is treated as text. Remove the formula, reformat column D to be All and reinsert the formula. Note that changing the type of a cell with an existing formula/data does not affect that formula/data. A cell must have its type selected _before_ the formula/data is entered, hence the need to remove/reinsert the formula.

[David12846]

acknak - My version is 3.3.0

RoryOF - I thought that might be it, but Formulas is not checked (Under Display: only Zero values, Comment indicator, Anchor, Text overflow, and Show references in color are checked)

[FJCC]

I suspect RoryOF's second suggestion, that the cell is formatted as text, is correct. Delete the cell contents, set the format to All, and enter the formula again.

[acknak]

Good catch, guys!

I didn't read carefully enough--it surely looks like column D is formatted as text, so it displays the formulas without interpretation.

BTW, it's not necessary to remove the formula; you just have to edit it. I usually just add a space at the end of the formula, then enter. That's good enough to force Calc to re-evaluate the entry. Saves a bit of typing.

Edit: PS:

acknak - My version is 3.3.0

Please update the information in your profile: How to update your software information signature

[ken johnson]

I usually just add a space at the end of the formula, then enter.

Great tip acknak!
I used to type an extra character then hit Backspace before Enter.
Not anymore.

Ken Johnson

[David12846]

Edit: Even simpler: What is Column D formatted to? Text? If so, then the formula is treated as text. Remove the formula, reformat column D to be All and reinsert the formula. Note that changing the type of a cell with an existing formula/data does not affect that formula/data. A cell must have its type selected _before_ the formula/data is entered, hence the need to remove/reinsert the formula.

Sorry for the delay, but RoryOF's instruction did the trick.

I wish this was listed in the HELP file about entering formulas. If it was, I didn't see it. Since "text" is what I was entering, and "text" was what I wanted to show in the cell, I originally set the format for that column to "text".

I wonder what other little innuendos I'm going to find

[Villeroy]

I wonder what other little innuendos I'm going to find

Learning by doing is the wrong way when it comes to programming. A spreadsheet is a simplified programming language for non-programmers and this language did not change very much since the 80ies.
My first spreadsheet (Lotus 1-2-3 for DOS, 1989) came as a book with 3 floppies. That text book (~300p.) included a full reference and all the things you need to know in order to solve most of the problems today's spreadsheet users still struggle with. It's all about value vs. formatting, text vs. number, absolute vs. relative reference, what is "True"? what is "False"? what is a date?

[TheGurkha]

I wonder what other little innuendos I'm going to find

Lol - you probably don't mean innuendo, but it would be great if OOo actually had innuendos in it. More people would read the manuals then.

http://en.wikipedia.org/wiki/Innuendo

[David12846]

TheGurkha - What I meant was, what other things am I going to stumble across that are not in the HELP pages, but are essential in order to make something work properly.

I'm just glad there is great bunch of people here to lend a hand.