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

Help Robot
1

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

2

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

1 Like
3

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
2 Likes
4

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

is doing a split on Linebreaks in Excel Cells

5

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
6

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:

2 Likes
7

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