Import .txt data into an excel work sheet

[robertoomaldo]

Hey there!!

I received a data set in a text file and i have to import it to an excel work sheet. the data is in the next format:


Variable1 = 1, 2, 3, 4, 5, 6, 7, 8, 9, 10; Variable2= 11, 12, 13, 14, 15, 16, 17, 18, 19, 20; Variable3 = 21,22, 23, 24, 25, 26, 27, 28, 29, 30; Variable4 = 31, 32, 33, 34, 35, 36, 37, 38,39, 40; Variable5= 41, 42, 43, 44, 45, 46, 47, 48, 49, 50; Variable6= 51, 52, 53, 54, 55, 56, 57, 58, 59, 60

where every column of a work sheet should be delimited by a ";" and every cell is delimited vertically by a ",". The "=" symbol separates the header name from the data.

In other words the txt file above should be represented in an table such as:

Variable1	Variable2	Variable3	Variable4	Variable5	Variable6
1	11	21	31	41	51
2	12	22	32	42	52
3	13	23	33	43	53
4	14	24	34	44	54
5	15	25	35	45	55
6	16	26	36	46	56
7	17	27	37	47	57
8	18	28	38	48	58
9	19	29	39	49	59
10	20	30	40	50	60

<tbody>
</tbody>

I tried to import the .txt file using the text import wizard of excel but I failed because excel does not recognize this type of delimitation. I am wondering if someone of you know a way to organize my .txt data as i show you in the table above. It can be using the <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">Vba</acronym> script or any other excel key.

 

[Rijnsent]

Hi Roberto,
VBA is probably the way to go, check out e.g. this tutorial: Excel VBA Programming - Opening Text and CSV files
Cheers,
Koen

 

[jimrward]

A few questions
Will it always be 6 variables and will it always be 10 lines of data or can you this change per file