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.