0

I am using a program called FilterPro to design an electronic filter. The filter has some associated data, which you can export to Excel. In FilterPro the data looks like this, where they use a decimal comma instead of a decimal dot.

enter image description here

FilterPro has a feature where you can export to Excel. However, when you export to Excel, they fit all the data into one column, where the different parameters are separated by a comma. So the numbers use decimal comma and they are also separated by a comma...

enter image description here

I want it such that each parameter (Frequency (Hz), Frequency (rad/sec),...) has their own column of data. However, because the numbers use decimal comma I cannot use Excel's text-to-columns to do this, because the numbers become wrong.

enter image description here

6
  • Can you change FilterPro to export with a different hundreds delimiter? Sep 27, 2022 at 10:22
  • @spikey_richie Unfortunately, no. It is a very primitive program.
    – Carl
    Sep 27, 2022 at 10:36
  • Have you had a look on the Texas Instruments KB for a solution? Sep 27, 2022 at 11:33
  • Does FilterPro enable a custom column delimiter within it's export process? Or even export to CSV with a custom column delimiter.
    – Isolated
    Sep 27, 2022 at 14:31
  • 1
    @spikey_richie No, you asked about a different hundreds delimiter (but I think you meant "thousands"). At any rate, I asked about a different column delimiter.
    – Isolated
    Sep 27, 2022 at 14:51

2 Answers 2

1

csv files are "ASCII", e.g. you may cat file (type file) and see the content.

Now, what you need is every other , converted to a .

Save this as e.g. cvt.py...

import sys

dot=1
c=sys.stdin.read(1)
while len(c)>0:
  if c!=',':
    print(c,end="")
  else: # c is a ","
    if not(dot): 
      print('.',end='')
    else:
      print(c,end='')
    dot=1-dot
  c=sys.stdin.read(1)

then do python3 cvt.py <file.csv >newfile.csv for your exported file.

Then try importing newfile.csv into Excel.

And yes, you need to install Python3,
Sorry, didn't realize you're using Windows until the end here, :-7

The Linux addict

1
  • I can see things to change (simplify), but it might also be easier to understand as is... ;-)
    – Hannu
    Sep 27, 2022 at 18:13
1

If you want it to come into existence, so to speak, in Excel as "all done" you need to use VBA, or an in between program, in the manner Hannu suggests or in other ways, such as a RegEx approach in, say, NotePad++.

I'm not qualified to help much there, and don't think it seems like you want that kind of fix, so I'll move on to "in Excel" ways to do it.

So you'd import, allowing the data to go into columns as Excel will when opening a .CSV file, or alternately, get it in as it appears in the second pic. (I believe renaming the file with a .TXT ending would allow that as it would bypass the auto approach a .CSV file gets. But however.) Assuming you get the multiple columns with each cell having half a number, there are multiple approaches.

One way to do it is to use a formula like:

=VALUE( A1 & "." & B1 )

in a cell somewhere convenient and copy it down as needed, as well as to every other column to its right until you account for all the columns in the source data. So formula result then empty then formula result then... etc.

(One could make each column's formula SPILL by using "A1:A12" and "B1:B12" and eliminate the need to "copy down.")

You'd then hide the empty columns, copy the output, and paste it where you desire the data to be.

But since you haven't many columns, why not automate that some more? Using various approaches, one can make the range used for the output contiguous so SPILL would be easy to make use of, but also, one could directly copy and paste. (HSTACK is what I show, but is very recent. INDEX using its "Areas" parameter (the 4th parameter) with that being an array constant like "{1,2,3,4}" or any approach that produces the required array constant, like SEQUENCE, or CHOOSE using an array constant in much the same way also come to mind. Probably others too. TEXTJOIN could surely be made to fit and so probably could XLOOKUP. So there are options other than HSTACK.)

Also, perhaps you have or will have users who use different system separators for their numbers. They might wish for your formula doing the conversion to produce a number value, no matter what, that their systems will then display for them. If you set the formula up for your own desired separators, it may not work for them.

That can be overcome by replacing VALUE with NUMBERVALUE. It does the same "text to numeric value" thing as VALUE but you can make certain the data converts to a number no matter the user's system separators by specifying the decimal separator Excel will use when trying to do it. With VALUE, if you concatenate the string using a comma where the decimal separation occurs, it works nicely, for you. If I open the spreadsheet and try my luck, Excel will not interpret the string as a number because it doesn't recognize it as one, or worse yet (my case), it will see that separator as the grouping separator and give me an integer like 561,876 not 561.876. But if you give the string to NUMBERVALUE and tell it the "," in the string is the decimal separator, it will know that on my machine too. And so it will give both of us the same number displayed as our operating systems have been told we prefer.

With HSTACK and NUMBERVALUE, a simple first three (six, but two for each output number) exported numbers would use a formula like this:

=HSTACK(  NUMBERVALUE(A1:A12&"."&B1:B12,"."),  NUMBERVALUE(C1:C12&"."&D1:D12,"."),  NUMBERVALUE(E1:E12&"."&F1:F12,".")  )

and all you need do is keep adding the sub-elements (NUMBERVALUE(E1:E12&"."&F1:F12,".")) changing the column letters until you have all the columns from the source data. Looks like seven sub-elements so less trouble to just paste one seven times and edit the letters than to use string building techniques to create the string and paste it into the formula. Also, easy enough to do it by hand that you needn't work up a way using INDIRECT and/or ADDRESS to do it.

So a single formula, no copying it and pasting it down and over and adding to it or cutting it back each new data set.

Since that works very easily, and fits any version of Excel (when using older functions to replace the HSTACK part), I won't go on to any others.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .