[Solved] Concatenate not combining text
-
- Posts: 65
- Joined: Fri Jan 09, 2009 6:46 am
[Solved] Concatenate not combining text
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.
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.
Reason: Tagged Solved, TheGurkha.
OOo 4.1.9 on Ms Windows 7
Re: CONCATENATE not combining text
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.
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.
AOO4/LO5 • Linux • Fedora 23
Re: CONCATENATE not combining text
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. |
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
-
- Posts: 65
- Joined: Fri Jan 09, 2009 6:46 am
Re: CONCATENATE not combining text
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)
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 4.1.9 on Ms Windows 7
Re: CONCATENATE not combining text
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.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: CONCATENATE not combining text
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.
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: |
Please update the information in your profile: How to update your software information signatureacknak - My version is 3.3.0
AOO4/LO5 • Linux • Fedora 23
-
- Volunteer
- Posts: 918
- Joined: Sun May 31, 2009 1:35 am
- Location: Sydney, Australia
Re: CONCATENATE not combining text
Great tip acknak!acknak wrote:I usually just add a space at the end of the formula, then enter.
I used to type an extra character then hit Backspace before Enter.
Not anymore.
Ken Johnson
AOO 4.1.3 on Ms Windows 10
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.
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.
-
- Posts: 65
- Joined: Fri Jan 09, 2009 6:46 am
Re: CONCATENATE not combining text
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.
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 4.1.9 on Ms Windows 7
Re: CONCATENATE not combining text
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.David12846 wrote: I wonder what other little innuendos I'm going to find
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 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: CONCATENATE not combining text
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.David12846 wrote:I wonder what other little innuendos I'm going to find
http://en.wikipedia.org/wiki/Innuendo
Ubuntu 14.10 Utopic Unicorn, LibreOffice Version: 4.3.3.2
Gurkha Welfare Trust
Gurkha Welfare Trust
-
- Posts: 65
- Joined: Fri Jan 09, 2009 6:46 am
Re: [Solved] Concatenate not combining text
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.
I'm just glad there is great bunch of people here to lend a hand.
OOo 4.1.9 on Ms Windows 7