How to split up a row of a specific excel column by "new line"?

Hello!

I would like to split the title up into two columns A and B.

image

For example,
Column A (Category) | Column B (Title)
Hunger Island | Madagascar in on the brink of famine.

May I ask how can I do this, please?

Thanks!

hailey

Hi @hailey7

Please try this,

  1. Read the excel sheet data in the datatable variable name dt.

  2. Use build datatable and create two columns category, title output variable name is dt2.

  3. Use for each row activity and pass dt.

Inside for each row activity use add datarow activity and in property give datatable as dt2, in arraryrow pass the value as

{currentrow(0).tostring.split(environment.newline.tochararray)(0),currentrow(0).tostring.split(environment.newline.tochararray)(1)}

Write the dt2 to the excel using write range

Thanks

Hi,

  1. First read your excel to datatable.
  2. open for each row loop
  3. Trim the column title like row(“category”).trim
  4. Split by new line as
    arr = row(“category”).tostring.Split(Environment.NewLine.TocharArray)
  5. Now assign arr(0) to category and arr(1) to title
1 Like

YourRowVar(YourColNameOrIndex).toString.Split({vbLF}, StringSplitOptions.RemoveEmptyEntries)

is doing a split on Linebreaks in Excel Cells

Thanks Manjula!

i’ve done this and it works.

Also, referred to this for additional reference (if it helps anyone else in the future too)

1 Like

Thanks! I didn’t try this out as I was already using “read range” on a specific excel sheet and didn’t want to build a data table again. I’ve looked at your code and adapted it for my work.

Thanks for the input! Trust that this will help for the rest too :slight_smile:

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.