SEMICOLONseparated

[Nemojoar10]

Hi! Have a question ...


Say I have some info that I entered in to a excellfil. Example: In column A1, it appears "NAME" so I enter a name in A2. Column B1, it appears "ADDRESS" so I fill in the address in B2, in C1 "TELE Nr" I fill tele Nr in C2. And so I continue with different information to column M. I continue down and fill in the next "person's" name in A3, in B3 address, phone number in C3, etc., etc. ... down to cell 50.

Now I want to get ALL information contained in A2, B2, C2, D2, etc., etc. until M2, in to A2. And all the info in A3, B3, C3, D3 until M3 gathered in A3 etc etc all the way to cell 50. So all the info in each row will be collected in Column A and down.

the thing is now ... I want the information from each column, from A2 up to M2 in A2 but separated with a "SEMICOLON" I want that the information from each column, now to appear in A2 separated by a, (semicolon) ; .

In column A1 will then name, address, phone number, etc. etc. to be separated by a semikoln as below:

name;address;telephone number; (information from A2, A3, A4, A5, etc., etc.) and all the way to the A50, B50, etc..

Please someone who understands this? I Uses Office 2011 on my Mac.

Thanks in advance!

[macropod]

Before we go down that path, what is the purpose? There may be better ways to do this.

[ArviL]

A. Set Window's regional settings to some, where semicolon is a list separator (German, Estonian, etc.). (Maybe it will work also, when you set the list separator for your current regional settings to semicolon.) Open your workbook, be sure that the worksheet you want to convert is only one in workbook or at least the first one. Save AS CSV (Comma Delimited).

B. This works when you don't have any commas in your current entries. Open your workbook, be sure that the worksheet you want to convert is only one in workbook or at least the first one. Save AS CSV (Comma Delimited) and close the csv-file. Open the csv-file with notepad, and replace all commas with semicolons. Save.

[macropod]

Originally Posted by ArviL

A. Set Window's regional settings ....

That's all very well, but the OP hasn't said the aim is to produce a text file. Besides he's using a Mac, which probably means he's not using Windows and, even if he were, mucking around with the Window's regional settings is not to be lightly advised.

[ArviL]

Originally Posted by macropod

That's all very well, but the OP hasn't said the aim is to produce a text file.

After his regional settings are set back again, when he opens the csv-file from Excel and saves it as Excel file, all entries will be in column A - separated with semicolons.

Besides he's using a Mac, which probably means he's not using Windows and, even if he were, mucking around with the Window's regional settings is not to be lightly advised.

This leaves him with the variant B then. Btw. even when his entries originally contain commas, then there is a workaround. Before saving the table as csv-file, he has to replace all commas with some other (unused) chartacter, p.e. "_". And after opening the csv-file and saving it to Excel format, he has to replace this character with comma everywhere again.

[macropod]

You don't seem to be paying attention:

Originally Posted by macropod

the OP hasn't said the aim is to produce a text file.

Your 'solution' won't help at all if the data aren't needed for a text file. What if they're simply required that way for feeding into another application, programmatically, without producing a text file? Besides, it's not at all hard to consolidata the data as requested - and outputting to a text file, without saving as csv then replacing commas.

[ArviL]

Originally Posted by macropod

You don't seem to be paying attention

Sorry! When I did read, that csv-file wasn't asked for, I logically assumed that Excel file was needed - because csv-file is a text file. Replace .csv with .txt or with nothing, and you get an ordinary text file.

[Nemojoar10]

Originally Posted by macropod

Before we go down that path, what is the purpose? There may be better ways to do this.

We are a company that collaborates with a larger company. We acquire customers for them, and then send it to them. But they want it in this format, as I describe.

[Nemojoar10]

Have got hold of a PC now. Saved my excel fil as CSV (comma delimited/separated). Then I opened the file with "NOTEPAD".

In NOTEPAD the information is separated by a semicolon. But in excel it looks just like usual excel.

[macropod]

Yes, for a system that already uses semi-colons for list separators, that's what you'll get. The same should apply to your Macs as well.

[Nemojoar10]

Originally Posted by ArviL

Sorry! When I did read, that csv-file wasn't asked for, I logically assumed that Excel file was needed - because csv-file is a text file. Replace .csv with .txt or with nothing, and you get an ordinary text file.

But after a have make the excel file, wich the information in it, is Semicolon separated, they want me to copy all info in kolumn A and paste it in a notepad sheet and save it as a .csv

So they require there different files. 1) a excel file save as .xlx, where the info looks like a usual excel file, the info in A1 should be in A1. The info in B1 should be in B1 and so on... 2) a "Semicolon separated" file where the info in every raw should be gathered in Kolumn A as below:
A1) name;adress; phone nr;
A2) name;adress; phone nr; and so on... and this file should also be saved as .xlx and the last file they require is 3) the one I did describe above. A notepad file saved as .csv where I copy the A kolumn from file "2" (the "Semicolon separated" file wich is saved as .xlx) and paste it in notepad and saved it as .csv

Nr 1 and 3 is no problem. But how do I do nr 2

[macropod]

For 2, after you create your CSV file, choose Data>Get External Data>From Text, navigate to and select your CSV file, choose Import>Finish>OK. Then choose Data>Connections>Remove>OK>Close.