How to split cell on the basis of certain conditions of other cell

Help
1

Hello all
How to split a cell on the basis of new line.Here in this excel sheet data needs to be extracted on the basis of new line in columns “From”,“To”,“Value”,“Total” as shown in figure.testdata1.xlsx (15.2 KB)

Screenshot%20(1)
Screenshot%20(1)1366×768 40.4 KB

1 Like
2

Really interested, I can’t wait to see who solves this one.

3

If generate datatable is used then what would be the input value?

4

Hi @mhk15

you can use for each row and based on column number you can use below code to get the expected output.

CellText.Split(Chr(10)).ToList.Item(0) ==> split the cell value and gives the first value.
CellText.Split(Chr(10)).ToList.Item(1) ==> split the cell value and gives the second value.

Hope this will help you.

Regards,
Vijay.

5

Thanks but what if the Number of items are not fixed.Here items are dynamic…Could please share the workaround?

6

Hi @mhk15

When you mentioned about number of items, do you multiple values in a single cell or multiple rows in excel sheet.

Case1: Multiple Rows

==> when you are reading excel sheet into datatable you will get all rows. so you can process all row values.

Case2: Multiple Values in a Single Cell

==> when you use CellText.Split(Chr(10)).ToList it will return a list which you can iterate through and then you can use another for each to get the values.

Hope this will help you.

Regards,
Vijay.