Parsing data for operation in Excel

artz

Well-known Member
Joined
Aug 11, 2002
Messages
830
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
something like this?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Frq[/td][td=bgcolor:#70AD47]Aml[/td][td=bgcolor:#70AD47]Pha[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
0.01​
[/td][td=bgcolor:#E2EFDA]
-1.075​
[/td][td=bgcolor:#E2EFDA]
170.221​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
0.01​
[/td][td]
-0.675​
[/td][td]
169.992​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
0.01​
[/td][td=bgcolor:#E2EFDA]
-0.275​
[/td][td=bgcolor:#E2EFDA]
169.757​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
0.011​
[/td][td]
0.125​
[/td][td]
169.516​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
0.011​
[/td][td=bgcolor:#E2EFDA]
0.525​
[/td][td=bgcolor:#E2EFDA]
169.27​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
0.011​
[/td][td]
0.925​
[/td][td]
169.017​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
0.011​
[/td][td=bgcolor:#E2EFDA]
1.325​
[/td][td=bgcolor:#E2EFDA]
168.759​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
0.012​
[/td][td]
1.725​
[/td][td]
168.495​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
0.012​
[/td][td=bgcolor:#E2EFDA]
2.125​
[/td][td=bgcolor:#E2EFDA]
168.224​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
0.012​
[/td][td]
2.525​
[/td][td]
167.947​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
0.013​
[/td][td=bgcolor:#E2EFDA]
2.925​
[/td][td=bgcolor:#E2EFDA]
167.663​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
0.013​
[/td][td]
3.325​
[/td][td]
167.372​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
0.013​
[/td][td=bgcolor:#E2EFDA]
3.724​
[/td][td=bgcolor:#E2EFDA]
167.074​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
0.013​
[/td][td]
4.124​
[/td][td]
166.769​
[/td][/tr]
[/table]


edit:
Ops, sorry, I missed you want vba
 
Last edited:
Upvote 0
Do you really want VBA ??
Formulae can do this

Excel 2007
ABCD
21.00000000000000e-002 (-1.07460150501695e+000dB,1.70221075386850e+002)1.00000000000000e-002-1.07460150501695e+0001.70221075386850e+002
31.02329299228075e-002 (-6.74640008303624e-001dB,1.69991552342250e+002)1.02329299228075e-002-6.74640008303624e-0011.69991552342250e+002
41.04712854805090e-002 (-2.74683403826346e-001dB,1.69756559710614e+002)1.04712854805090e-002-2.74683403826346e-0011.69756559710614e+002
Sheet1
Cell Formulas
RangeFormula
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)
 
Last edited:
Upvote 0
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 ?
 
Upvote 0
something like this?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Frq[/td][td=bgcolor:#70AD47]Aml[/td][td=bgcolor:#70AD47]Pha[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
0.01​
[/td][td=bgcolor:#E2EFDA]
-1.075​
[/td][td=bgcolor:#E2EFDA]
170.221​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
0.01​
[/td][td]
-0.675​
[/td][td]
169.992​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
0.01​
[/td][td=bgcolor:#E2EFDA]
-0.275​
[/td][td=bgcolor:#E2EFDA]
169.757​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
0.011​
[/td][td]
0.125​
[/td][td]
169.516​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
0.011​
[/td][td=bgcolor:#E2EFDA]
0.525​
[/td][td=bgcolor:#E2EFDA]
169.27​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
0.011​
[/td][td]
0.925​
[/td][td]
169.017​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
0.011​
[/td][td=bgcolor:#E2EFDA]
1.325​
[/td][td=bgcolor:#E2EFDA]
168.759​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
0.012​
[/td][td]
1.725​
[/td][td]
168.495​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
0.012​
[/td][td=bgcolor:#E2EFDA]
2.125​
[/td][td=bgcolor:#E2EFDA]
168.224​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
0.012​
[/td][td]
2.525​
[/td][td]
167.947​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
0.013​
[/td][td=bgcolor:#E2EFDA]
2.925​
[/td][td=bgcolor:#E2EFDA]
167.663​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
0.013​
[/td][td]
3.325​
[/td][td]
167.372​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
0.013​
[/td][td=bgcolor:#E2EFDA]
3.724​
[/td][td=bgcolor:#E2EFDA]
167.074​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
0.013​
[/td][td]
4.124​
[/td][td]
166.769​
[/td][/tr]
[/table]


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
 
Upvote 0
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
ABCD
11.00000000000000e-002 (-1.07460150501695e+000dB,1.70221075386850e+002)0.01-1.075170.221
21.02329299228075e-002 (-6.74640008303624e-001dB,1.69991552342250e+002)0.01-0.675169.992
31.04712854805090e-002 (-2.74683403826346e-001dB,1.69756559710614e+002)0.01-0.275169.757
41.07151930523761e-002 (1.25267781027906e-001dB,1.69515961436982e+002)0.0110.125169.516
51.09647819614318e-002 (5.25212965403845e-001dB,1.69269617699112e+002)0.0110.525169.27
61.12201845430196e-002 (9.25151509702781e-001dB,1.69017384779520e+002)0.0110.925169.017
71.14815362149688e-002 (1.32508270979400e+000dB,1.68759114931987e+002)0.0111.325168.759
81.17489755493953e-002 (1.72500579066355e+000dB,1.68494656242269e+002)0.0121.725168.495
91.20226443461741e-002 (2.12491989944593e+000dB,1.68223852482718e+002)0.0122.125168.224
101.23026877081238e-002 (2.52482409777929e+000dB,1.67946542960527e+002)0.0122.525167.947
111.25892541179417e-002 (2.92471735341947e+000dB,1.67662562359300e+002)0.0132.925167.663
121.28824955169313e-002 (3.32459853104108e+000dB,1.67371740573617e+002)0.0133.325167.372
131.31825673855641e-002 (3.72446638214826e+000dB,1.67073902536292e+002)0.0133.724167.074
141.34896288259165e-002 (4.12431953400941e+000dB,1.66768868037971e+002)0.0134.124166.769
Sheet403
Cell Formulas
RangeFormula
B1=ROUND(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1,"(",""),"dB",""),","," "),"°)","")," ",REPT(" ",99)),COLUMNS($B1:B1)*99-98,99),3)


EDIT: fixed error.
 
Last edited:
Upvote 0
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
 
Upvote 0
Check my formula in Post # 6, it should be producing the results you want.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top