Forum Discussion

Antonino2023's avatar
Antonino2023
Brass Contributor
May 23, 2023
Solved

How to convert csv list to column WITHIN an excel Table (no VBA)

Hello, 

 

I am looking for an equation that does the following:

 

Change GroupChange Group Conversion
5,6,7,8,95
5,6,7,8,96
5,6,7,8,97
5,6,7,8,98
5,6,7,8,99
  

 

Essentially, the table from above is an excel Table (lets call it "Analysis_T"). I am looking for a single formula that can pull out the i-th value in the csv list where i is the data row # of Analysis_T. For example, row 1 should pull out "5", row 2 should pull out "6", and so on. The cells in the "Change Group" column will always be the same list as shown. What should be different is the adjacent value in the "Change Group Conversion" column as specified (both columns should remain as text).

 

Does anyone have suggestions for this task?

BI & Data Analysis
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    May 23, 2023

    Antonino2023 

    Perhaps

     

    =IFERROR(INDEX(TEXTSPLIT([@[Change Group]], ","), ROW([@[Change Group]])-ROW(Analysis_T[[#Headers],[Change Group]])), "")

Resources