0

I'm trying to process data in CSV format using Excel.

I can import the CSV file, but I need to process this data in various ways. For example, I have a coloumn of hex values that need to be plotted. So I need to convert all the rows in this column to decimal numbers.

The imported CSV is in an Excel table, and I have tried using the typical =HEX2DEC('Data'!$A2:A), but it doesn't work at all.

The file I am processing has varying length. Some files are 20 rows long, others are 600,000. Obviously I don't want to copy-paste even 20 lines, let alone 600K lines.

I am required to use excel for this and I don't have VBA access. Is what I am trying to do even possible?

Edit 0:

Here is the section of the data that I am trying to use HEX2DEC on data table 1 This is A and B coloumn. B1 has some headers in it that I don't care about right now. This will sometimes be from B2 to B500, or B2 to B5. These are all on Sheet1 (Which I have named Data). I can perform HEX2DEC on one cell, Sheet 2 formula. But even double clicking on the green corner on the bottom right will not extend it down to match the size of the data on Sheet1. It would also be nice if there was a way to automate that such that I can just import the CSV data to Sheet1 and have Sheet2 automatically update the fields and adjust to the size of the data imported.

r_fault
  • 11
  • 2
  • 1
    I edited your question to try to make it more clear, but I have some questions. 1. You said "The CSV is a data table", do you mean that the *imported* CSV data has been made into an Excel named table? 2. "It doesn't work at all" isn't very descriptive or helpful. If you try to convert just one cell with Hex2Dec, what error do you get? 3. You wrote the formula as =HEX2DEC('Data'!$A2:A). What is the actual formula? 4. Could you upload a screenshot of a portion of your Excel sheet? Thanks. – Bandersnatch Apr 25 '18 at 21:34
  • If the filling down is your problem and not the actual Hex2Dec conversion, see [this answer by teylyn](https://superuser.com/questions/948175/fill-a-large-range-with-a-formula-in-excel-without-mouse-dragging-to-extend) – Bandersnatch Apr 25 '18 at 21:41
  • @Bandersnatch 1. Yes. The CSV has been imported to sheet 1 as data. Sorry for the confusion there. 2. I meant to say that it doesn't work to extend it to the length of the data. It will only work if I reference one cell. Say $B2. Not even if I do $B2:$B3. 3. Included in screen shot. 4 yes. Also, that answer provided does not work. – r_fault Apr 25 '18 at 22:35
  • So you have made the imported data into a pivot table? The formula in your screenshot isn't anything close to the one in your question. If you are willing to **clearly** explain what you want to do and the problems you're having, people here will try to help you. If not, it'll be ur_fault. – Bandersnatch Apr 25 '18 at 23:59
  • And again, please *clearly* explain what happens when you try to fill the formula down one cell? 10 cells? If you double click on the green triangle, what *exactly* happens? Does it extend any cells at all? – Bandersnatch Apr 26 '18 at 00:15

1 Answers1

1

After trying several things and coming to a new problem, my question was answered here.

The HEX2DEC function can only take ONE cell as an input, not a range of cells. The function can also not flash fill with the bottom right corner.

Placing the function right next to the last cell accomplishes the task of flash filling n-lines that exist in the table created from Data -> Get External Data -> From Text

Meaning my data rows for an imported table are 2x as long as the CSV being imported.

r_fault
  • 11
  • 2