I've been given a job to do by one of my customers at work involving copying approx 4,000 4 and 5 digit numbers from a spreadsheet into one of our systems.

Unfortunately, every single set of numbers is in it's own column on the spreadsheet and to put them into the system I have they have to be separated only by commas (e.g. 1234,1235,1236 etc)

Spent a good hour trying to work out if and how I can transfer the data from individual cells into either one cell (or a few more if the data would be too large) and add in commas between the sets of numbers.

There is a Concatenate function, but everything I have tried seems to fail (short of entering every cell into the formula which defeats the purpose of trying to do it.

Does anyone know if there is a way that can be done or am I going to have to split up the work between everyone to enter the data manually?

Any help/advice would be greatly appreciated.

Excel is an evil bitch of an application. Totally inappropriate for 90% of the tasks it's used for (I once saw a 180Gb excel spreadsheet being used as a database store that was regularly sent between managers as an attachment! ).

However let's deal with the basics, you have the spreadsheet as the source, what is the destination? Another spreadsheet, a database? etc?

It's an application where the I.D.s for machines are entered in the format above and it then sends SMS messages to the machines to carry out whatever instruction you have set.

Format of entering the information is much like the reply box on this forum (hence why commas are needed so there is a form of separation between the machine I.D.s)

I hate Excel with every fibre of my being purely because of what you have mentioned. Companies using it for storing every bit of information they have..

So you have to manually enter in 1000's of numbers into a text box?

Are those numbers stored elsewhere, or are they deleted once they are used?

It sounds like a dog of an app. Is this something you have written or is it a commercial program?

Don't have Excel at home, but I'm sure from memory you can either save or export the file as raw text, comma delimitted. Look under "Save As…" and then the file type drop down menu.

There are several different ways to output from Excel; CSV, XML, plain text, and you can also link it direct to a database meaning you don't need to save the file at all.

It all depends on what format the application needs it in.

Basically I just need the numbers changed from being separate to them all being linked together separated only by a comma. I can then copy+paste them into the application and process them.

e.g. column a has 1234 in it, b has 1235, c has 1236 and I'd like it so they are all together as 1234,1235,1236 either in a cell or in a Word document. Anything where I can just copy them into the app

As I said, there are 4000 of them that need added into it so if I had them all as a list I could copy over into the app rather than having to get everyone to do 200odd each, inputting them manually I'd be a very happy man!

There is probably a simple solution where I can do this but I am just not seeing it. Sorry if the explanation of what I am trying to do is a bit crap, I can generally cope OK when working with these programs, but trying to explain something is a whole different story.

P.S. Ric - Can't take any of the credit for the poor design of the app. Problem is the company is unique in the technology it uses, has only been going for 5 years and the numerous I.T. staff are working on developments for the systems we produce. Unfortunately, I think a lot of the internal applications and systems were rushed in and now the company is expanding so fast there isn't the time to upgrade them and make them more user friendly. Great fun working in Customer Relations when you get requests like this!

As Mad Cyril said, save it as a csv, then all you have to do is open the new file in notepad, copy, paste and you're there.

You could also do something like =A1&"," in the second row and paste for all the columns, then copy the row and paste into the input box.

If the faults in your apps are wasting a lot of your time (like an hour trying to import data from a common format) then they need fixed asap. If the company is growing the amount of time wasted working round something that can be sorted out relatively easily is only going to increase.

It's higher up in this thread FFS.

Sorry, been a long day. Will do the csv save tomorrow. I had tried that earlier but for some reason (complete incompetence most probably) thought that it should be opened again in Excel. Thanks for your help. Now I have to find out the maximum number of the machine I.D.s I can put into the application at once. Have a feeling it may fail if all 4,000 are sent through it at once!

I'm only 4 days into a work trial for the company (unpaid!), I shouldn't have to be bothering with this stuff yet!

Thanks again.

Excel is a brilliant application. Microsoft rightly get a load of abuse for a lot of things, but Excel is great.

I'd say the vast majority of organisations, big or small, regardless of how good their ERP application is, probably make some of their most critical decisions based on Excel spreadsheets