Data fromto notepad

QuestionEdit

QUESTION: I have a few thousand records down column A in Excel. I'm trying to export to txt but by getting all the data in a single row (unwrapped). For example .xls> apples; oranges; bananas; converted to .txt> apples; oranges; bananas;

This is probably lower grade knowledge for the experts on this site but I'm struggling for days already :( Please advise. Tks Randolph

ANSWER: Good day,

I don't know how many data you want in a text row, but this will help you:

=A3&"; "&A4&"; "&A5&"; "&A6&"; "&A7&"; "&A8&"; "&A9&"; "&A10&"; "&A11&"; "&A12&"; "&A13&"; "&A14&"; "&A15&"; "&A16&"; "&A17&"; "&A18&"; "&A19&"; "&A20&"; "&A21&"; "&A22&"; "&A23&"; "&A24&"; "&A25&"; "&A26&"; "&A27&"; "&A28&"; "&A29&"; "&A30&"; "&A31

This is a short example and you can continue just by adding a cell with &";<space>"& this gives the ; and a space between the numbers (if your cells only contain numbers).

After this you can select the calls that are newly created and copy past into WordPad or notepad.

---------- FOLLOW-UP ----------

QUESTION: Hey That works... only problem is that I'm dealing with 15,000 records down column A, so it will take a few days to type out the formula from A1&"; "&A2&"; "&A3&"... all the way to .."&A14999&"; "&A15000&";

Is there not a shortcut formula? Something like A1&"; "~include all~"; "&A15000&; I need to send you a prezzie if you have a solution!

-)

AnswerEdit

Glad to help hey,

I know it is quite a lot of rows you have, I think there might be a VBA formula to speed the whole thing up, but I don¡¯t know VBA yet.

I do have another option for you, will take a bit of time but not a day.

Section 1: Firstly: in the first cell were you want your text cell to be in number it 1, 2) Next to the cell numbered 1, insert the formula =A3&"; "&A4&"; "&A5&"; "&A6&"; "&A7... to A23 (20 rows in total)

3) Then in row 24 copy the formula over and add an IF functions to the formula:

=IF(AO3="","",A24&"; "&A25&"; "&A26&"; "&A27&"; "&A28&"; "&A29&"; "&A30&"; "&A31&"; "&A32&"; "&A33&"; "&A34&"; "&A35&"; "&A36&"; "&A37&"; "&A38&"; "&A39&"; "&A40&"; "&A41&"; "&A42&"; "&A43&"; "&A44)

4) In the cell just left of the text cell insert this formula: =IF(AN3="","",AN3+1)

5) Drag both the cells to the bottom of your data till 15 000

The formula will calculate all the text cells for you in intervals of 20.

Section 2: Now next to get the text in order:

1) In column AQ number the cells from 1 to 750 (15000/20) 2) In column AR formula =VLOOKUP($AQ3,$AN$3:$AO$809,2,FALSE)

Then you do about the same as in the first instance to get that text together with the other 20 text lines just created with the VLOOKUP function.

Repeat this till you get all your 15000 data (ducks) in a row.

Oh my columns in my formulas are a bit down the alphabet but you can just edit them to be where they should be, I had to do this on one of my sheets that I already have.

Also a tip in the formula of

=A24&"; "&A25&"; "&A26&"; "&A27&"; "&A28&"; "&A29&"; "&A30&"; "&A31&"; "&A32&"; "&A33&"; "&A34&"; "&A35&"; "&A36&"; "&A37&"; "&A38&"; "&A39&"; "&A40&"; "&A41&"; "&A42&"; "&A43&"; "&A44

Select the cell and type &"; "& and then copy the &"; "& and just past it after you selected a cell.

Good luck and please let me know if you got it correct