Split value after 3rd special character

Hi guys,
I want to split the cell values after the second “&” , and write the third value to another excel sheet . Can anyone please suggest an approach, kindly refer the screenshot below :
ampseperator
From this column, I want 275,275,6.25,6.25 and so on.

Kindly suggest, Thanks in advance.

1 Like

Hi @Shailesh123

Please read this article for your reference.

cheers :smiley:

Happy learning :smiley:

1 Like

@pattyricarte- these methods are all taking the values as Array , So if I had to read values from that array its only using Index that I would be able to access. I want to read it as a variable so that I can directly write it to excel

1 Like

Hi @Shailesh123

Scenario
1.Use Excel application activities.
2.Create a variable Datatable Type
3.Use Read Range Activities.
4.Assign the variable to the output of Read Range Activities
5.Use ForeachRow Activities and iterate to the end of the file

Note
Upond doing iteration with in dattable use split method to get your desire value
Variable.Split(New Char() {"&"c}) then it will create an array of it
Then get the third value based index start in zero.

cheers :smiley:

Happy learning :smiley:

1 Like

@Shailesh123,

Try with substring functionality using last index of the “&” and the length of the string.

"0.5&550&275".Substring(("0.5&550&275".ToString.LastIndexOf("&")+1), "0.5&550&275".ToString.Length - ("0.5&550&275".ToString.LastIndexOf("&")+1))

@Shailesh123

Where do you want the desired list of strings to be stored? I will consider that you want them in a separate row in this table, lets say column “Result”

Please follow these steps:-

  1. Read Range into variable dtList
  2. Add Data column- “Result”
  3. For each row in dtList
  4. split strings in the following way:-
    a. strArray = row(“M_triplate1”).tostring.split("&".toCharArray)
    b. row(“Result”) = strArray(2).tostring
  5. Now you have all the values in the column “Result”

I tried it. Working fine.

Thanks and Regards,
@hacky

1 Like

1,Create New DataTable with Build DataTable activity and store the output variable in DT1

2,Read the excel file and store the output in DataTable variable in DT2

3,Iterate the DataTable (DT2) with for each row activity

4,Inside the for each row activity place add row item activity and pass the below code in the form of array in add row item activity (Give output variable as DT1)

{row(0).split("&".ToCharArray)(2)}

5,place write range activity outside the for each row and pass the DT1 in DataTable and write the DataTable wherever you want.

Cheers…! :grinning::grinning::grinning:

Split&.zip (24.4 KB)

I have created a workflow based on your requirement.