Parsing data for operation in Excel

[artz]

Hi,

I am using a circuit simulation program called LTSPICE. It has an option to export chart data to a text file. Unfortunately, the format is not directly compatible with Excel. A snippet of the file output is shown below:

Freq. V(out)
1.00000000000000e-002 (-1.07460150501695e+000dB,1.70221075386850e+002°)
1.02329299228075e-002 (-6.74640008303624e-001dB,1.69991552342250e+002°)
1.04712854805090e-002 (-2.74683403826346e-001dB,1.69756559710614e+002°)
1.07151930523761e-002 (1.25267781027906e-001dB,1.69515961436982e+002°)
1.09647819614318e-002 (5.25212965403845e-001dB,1.69269617699112e+002°)
1.12201845430196e-002 (9.25151509702781e-001dB,1.69017384779520e+002°)
1.14815362149688e-002 (1.32508270979400e+000dB,1.68759114931987e+002°)
1.17489755493953e-002 (1.72500579066355e+000dB,1.68494656242269e+002°)
1.20226443461741e-002 (2.12491989944593e+000dB,1.68223852482718e+002°)
1.23026877081238e-002 (2.52482409777929e+000dB,1.67946542960527e+002°)
1.25892541179417e-002 (2.92471735341947e+000dB,1.67662562359300e+002°)
1.28824955169313e-002 (3.32459853104108e+000dB,1.67371740573617e+002°)
1.31825673855641e-002 (3.72446638214826e+000dB,1.67073902536292e+002°)
1.34896288259165e-002 (4.12431953400941e+000dB,1.66768868037971e+002°)

I would like to have 3 columns of data: Freq., Amplitude, and Phase. The Amplitude needs the dB removed and the Phase needs the ° removed. The parentheses also need to be removed. Also, having the data delimited by commas would be best. It would also be nice to have the data limited to 3 decimal points. The files are of variable length in rows.

Could anyone in the Forum help with a VBA macro that could do this processing?

Any help would be greatly appreciated.

Thanks,

Art

 

[sandy666]

something like this?

Frq	Aml	Pha
0.01​	-1.075​	170.221​
0.01​	-0.675​	169.992​
0.01​	-0.275​	169.757​
0.011​	0.125​	169.516​
0.011​	0.525​	169.27​
0.011​	0.925​	169.017​
0.011​	1.325​	168.759​
0.012​	1.725​	168.495​
0.012​	2.125​	168.224​
0.012​	2.525​	167.947​
0.013​	2.925​	167.663​
0.013​	3.325​	167.372​
0.013​	3.724​	167.074​
0.013​	4.124​	166.769​

edit:
Ops, sorry, I missed you want vba

 

[Michael M]

Do you really want VBA ??
Formulae can do this

Excel 2007
	A	B	C	D
2	1.00000000000000e-002 (-1.07460150501695e+000dB,1.70221075386850e+002)	1.00000000000000e-002	-1.07460150501695e+000	1.70221075386850e+002
3	1.02329299228075e-002 (-6.74640008303624e-001dB,1.69991552342250e+002)	1.02329299228075e-002	-6.74640008303624e-001	1.69991552342250e+002
4	1.04712854805090e-002 (-2.74683403826346e-001dB,1.69756559710614e+002)	1.04712854805090e-002	-2.74683403826346e-001	1.69756559710614e+002
Sheet1

Cell Formulas
Range	Formula
B2	=LEFT(A2,21)
C2	=MID(A2,SEARCH("(",A2)+1,SEARCH(",",A2)-SEARCH("(",A2)-3)
D2	=MID(A2,SEARCH(",",A2)+1,SEARCH(")",A2)-SEARCH(",",A2)-2)

 

[jtakw]

Hi,
having the data delimited by commas would be best.
Thanks,
Art

Hi,

I'm also going to propose a formula solution, but needs to clarify, by the above, do you mean you want the results all in a Single cell per row ?

 

[artz]

something like this?

Frq	Aml	Pha
0.01​	-1.075​	170.221​
0.01​	-0.675​	169.992​
0.01​	-0.275​	169.757​
0.011​	0.125​	169.516​
0.011​	0.525​	169.27​
0.011​	0.925​	169.017​
0.011​	1.325​	168.759​
0.012​	1.725​	168.495​
0.012​	2.125​	168.224​
0.012​	2.525​	167.947​
0.013​	2.925​	167.663​
0.013​	3.325​	167.372​
0.013​	3.724​	167.074​
0.013​	4.124​	166.769​

edit:
Ops, sorry, I missed you want vba

Hi Sandy,

This looks mostly right. Yes, VBA is preferred. I can hack at it but with cell formulas I have more difficulty.

Thanks,

Art

 

[jtakw]

Well, I guess you want the results in 3 Separate Columns, so I don't understand the "comma delimited" part...

Formula copied down and across:

Book1
	A	B	C	D
1	1.00000000000000e-002 (-1.07460150501695e+000dB,1.70221075386850e+002)	0.01	-1.075	170.221
2	1.02329299228075e-002 (-6.74640008303624e-001dB,1.69991552342250e+002)	0.01	-0.675	169.992
3	1.04712854805090e-002 (-2.74683403826346e-001dB,1.69756559710614e+002)	0.01	-0.275	169.757
4	1.07151930523761e-002 (1.25267781027906e-001dB,1.69515961436982e+002)	0.011	0.125	169.516
5	1.09647819614318e-002 (5.25212965403845e-001dB,1.69269617699112e+002)	0.011	0.525	169.27
6	1.12201845430196e-002 (9.25151509702781e-001dB,1.69017384779520e+002)	0.011	0.925	169.017
7	1.14815362149688e-002 (1.32508270979400e+000dB,1.68759114931987e+002)	0.011	1.325	168.759
8	1.17489755493953e-002 (1.72500579066355e+000dB,1.68494656242269e+002)	0.012	1.725	168.495
9	1.20226443461741e-002 (2.12491989944593e+000dB,1.68223852482718e+002)	0.012	2.125	168.224
10	1.23026877081238e-002 (2.52482409777929e+000dB,1.67946542960527e+002)	0.012	2.525	167.947
11	1.25892541179417e-002 (2.92471735341947e+000dB,1.67662562359300e+002)	0.013	2.925	167.663
12	1.28824955169313e-002 (3.32459853104108e+000dB,1.67371740573617e+002)	0.013	3.325	167.372
13	1.31825673855641e-002 (3.72446638214826e+000dB,1.67073902536292e+002)	0.013	3.724	167.074
14	1.34896288259165e-002 (4.12431953400941e+000dB,1.66768868037971e+002)	0.013	4.124	166.769
Sheet403

Cell Formulas
Range	Formula
B1	=ROUND(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1,"(",""),"dB",""),","," "),"°)","")," ",REPT(" ",99)),COLUMNS($B1:B1)*99-98,99),3)

EDIT: fixed error.

 

[artz]

Hi Michael,

Your formula works but I have 2 issues:

Excel will not allow me to truncate/round to 2 or 3 decimal places that your cell formula creates. Also, I have absolutely no idea what your cell formulas are doing, that why I prefer VBA.

Thanks,

Art

 

[sandy666]

This looks mostly right. Yes, VBA is preferred. I can hack at it but with cell formulas I have more difficulty.

this is not formula(s) but PowerQuery

 

[jtakw]

Check my formula in Post # 6, it should be producing the results you want.

 

[artz]

Hi jtakw,

Probably I missed something with the csv request. In a perfect world, I would be able to use Excel to open the txt file described in the snippet, format to 3 columns, and then ask to save in XL, csv, or txt format.

That's the cat's meow.

Thanks,

Art