[Solved] Concatenate not combining text

Discuss the spreadsheet application

[Solved] Concatenate not combining text

Postby David12846 » Wed Mar 21, 2012 5:50 am

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.
Last edited by TheGurkha on Sat Mar 31, 2012 8:48 pm, edited 1 time in total.
Reason: Tagged Solved, TheGurkha.
OOo 3.0.X on Ms Windows XP
David12846
 
Posts: 13
Joined: Fri Jan 09, 2009 6:46 am

Re: CONCATENATE not combining text

Postby acknak » Wed Mar 21, 2012 8:14 am

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.
AOO 4 • Linux • Fedora 17
User avatar
acknak
Moderator
 
Posts: 17420
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: CONCATENATE not combining text

Postby RoryOF » Wed Mar 21, 2012 8:39 am

Might the installation be set to display formulae not values? From the Help:
Code: Select all   Expand viewCollapse view
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. 
Apache OpenOffice 4.0.1 on Xubuntu 13.10 and Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 14367
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: CONCATENATE not combining text

Postby David12846 » Wed Mar 21, 2012 2:12 pm

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)
OOo 3.0.X on Ms Windows XP
David12846
 
Posts: 13
Joined: Fri Jan 09, 2009 6:46 am

Re: CONCATENATE not combining text

Postby FJCC » Wed Mar 21, 2012 2:25 pm

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.
AOO 3.4 or 4.0 on MS Windows XP ( before 2013-08-03) or Windows 7
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 3678
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: CONCATENATE not combining text

Postby acknak » Wed Mar 21, 2012 3:16 pm

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
AOO 4 • Linux • Fedora 17
User avatar
acknak
Moderator
 
Posts: 17420
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: CONCATENATE not combining text

Postby ken johnson » Wed Mar 21, 2012 3:28 pm

acknak wrote: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
AOO 4.0.1 on Ms Windows 7
Help improve this forum by clicking the EDIT button on your first post then adding "[Solved]" to its heading once a solution to your problem has been provided.
ken johnson
Volunteer
 
Posts: 840
Joined: Sun May 31, 2009 1:35 am
Location: Sydney, Australia

Re: CONCATENATE not combining text

Postby David12846 » Sat Mar 31, 2012 6:37 pm

RoryOF wrote:
 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. :super:

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 :(
OOo 3.0.X on Ms Windows XP
David12846
 
Posts: 13
Joined: Fri Jan 09, 2009 6:46 am

Re: CONCATENATE not combining text

Postby Villeroy » Sat Mar 31, 2012 7:09 pm

David12846 wrote: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?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17305
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: CONCATENATE not combining text

Postby TheGurkha » Sat Mar 31, 2012 8:10 pm

David12846 wrote: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
Ubuntu 13.04 Raring ringtail, LibO 4.0.2.2 (Build ID 400m0 (Build:2))
Gurkha Welfare Trust
User avatar
TheGurkha
Moderator
 
Posts: 6426
Joined: Thu Mar 13, 2008 12:13 pm
Location: North Wales, UK.

Re: [Solved] Concatenate not combining text

Postby David12846 » Sun Apr 01, 2012 1:29 am

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.
OOo 3.0.X on Ms Windows XP
David12846
 
Posts: 13
Joined: Fri Jan 09, 2009 6:46 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 22 guests