# Excel formula doesn't want to work

**URL:** <https://boards.straightdope.com/t/excel-formula-doesnt-want-to-work/819688>\
**Category:** Factual Questions\
**Created:** [August 15, 2018, 9:10pm UTC](https://boards.straightdope.com/t/excel-formula-doesnt-want-to-work/819688 "2018-08-15T21:10:16Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![PoppaSan](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/poppasan/32/189_2.png) [@PoppaSan](https://boards.straightdope.com/u/PoppaSan)\
**Post date:** [August 15, 2018, 9:10pm UTC](https://boards.straightdope.com/t/excel-formula-doesnt-want-to-work/819688/1 "2018-08-15T21:10:16Z")

</div>

TL;DR: Concatenate function doesn’t do its thing and I don’t know why.

I have a \*.txt file that I want to be more user friendly. It is a data dump of a several thousand component parts list kept in SAP. The SAP software doesn’t allow me many options to do anything. I am at a restricted low level user level. I want the info to stop having to wait for the login and server to decide to give me access for a quick look up. It lets me do an export of data in a restricted set of options, CSV is not one of them. A text file is the only one I have software to access it with. My options are a recent Win 10 system running last years Excel and a Win 7 system with Excel 2008 era. I am also in a restrictive paranoid with security corporate IT environment.

Just opening the file with Excel gives all the data in the first column. Using the import wizard, I can get the data to spread across several columns. Space separated data gets it closest to being usable. Some rows have long descriptions which make fixed field width unusable.

I want a simple way to combine columns for 3500+ rows. _ **MERGE** _ will delete all date after the first column. The “manual” says I should be able to use \*\*\*CONCATENATE \*\*\*or _ **CONCAT** _. Whether I type in the letters and cells or use the wizard it keeps showing the formula. The data does not merge. Yes I have turned on and off the show formula icon. When I do the formula check, it says I have a constant in the formula which is why I assume it fails. It worked once but when I copy and paste the formula down one row and change the row number it didn’t work again.

Example: All cells are designated as text. Row 6, Columns E-J contain _ORDER THIS PART FROM ACME INDUSTRIES_. I should be able to put in Rox 6 cell C _=CONCATENATE(E6:J6)_ and get those cells as one text block. I only get the formula. When I try _E6 & F6 & G6 & H6_ etc I get nothing but what I type. Using the formula wizard does not change things \<- this is the part that really annoys me, the wizard should do it correctly. Other formulas work like _ **SUM** _ and\*\*\* DATE\*\*\*, just not the one I want. _ **TEXTJOIN** _ doesn’t appear to be an option either.  
After this is solved, I need to remember how to fill down 3500 rows with the relevant row changing. The dollar sign thing fails too.

---

<div class="post-metadata">

**Author:** ![tim-n-va](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/tim-n-va/32/3001_2.png) [@tim-n-va](https://boards.straightdope.com/u/tim-n-va)\
**Post date:** [August 15, 2018, 9:17pm UTC](https://boards.straightdope.com/t/excel-formula-doesnt-want-to-work/819688/2 "2018-08-15T21:17:58Z")

</div>

The cell with the formula might be formatted as text due to the import. Makes sure it is “General”.

---

<div class="post-metadata">

**Author:** ![PoppaSan](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/poppasan/32/189_2.png) [@PoppaSan](https://boards.straightdope.com/u/PoppaSan)\
**Post date:** [August 18, 2018, 2:26am UTC](https://boards.straightdope.com/t/excel-formula-doesnt-want-to-work/819688/3 "2018-08-18T02:26:36Z")

</div>

Nailed it in one. Had time at work yesterday to check it. Thankx. Mentioned this to my son and he went well duh!