How do we split the line with the "at" character?

I have an excel file, one of the columns has some data, I have to write that column in 2 different columns.
for ex : Service Advisor at ABC company
I have to write the word before at in a different column and the word after at in a different column, how can I do that?

Hi @swati_nagar

Check with the reference link below!

Regards

@swati_nagar

Use the below expression to split

Split(Currentrow(“Columnname”).toString,“at”)(0).trim holds the value before at

Split(Currentrow(“Columnname”).toString,“at”)(1).trim holds the value after at

Regards

@swati_nagar

Use Read Range activity to read the excel and save the data to Datatable

Use For Each row activity, Inside that you can assign and write as

strCompany = Row(“ColumnName”).ToString

If you are using old version of Studio then in For Each row it is Row, Else it is CurrentRow

Now write as below for the split

image

Inside the For Each use Assign activity again and write as below

DatatableVariable.Item(“ColumnName”) = SplitVariable

Which is as mentioned in the Messagebox from the above image

Hope this may help you

Thanks

Thanks Srinivas,
This method is working but error is coming on the row where “at” word is not there, then what condition should I write in it?
I am write this condition in for each row loop.


It is showing the error .

@swati_nagar

Then put a if condition and write as

Samplestring.Contains(“at”)

Then place your split logic

Else you can leave that

Hope this helps you

Thanks

Thanks all
Its Working.

1 Like

Read Range the sheet into a datatable. Manipulate the datatable to get the data how you want. Write Range back to the sheet.