Split value after 3rd special character

[Shailesh123 (Shailesh Pathak)]

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 :

From this column, I want 275,275,6.25,6.25 and so on.

Kindly suggest, Thanks in advance.

[pattyricarte (Patty Boy)]

Hi @Shailesh123

Please read this article for your reference.

How to manipulate a part of string: Split, Trim, Substring, Replace, Remove, Left, Right

Hi, if you are a beginner in the world of automation and would like to know how you can manipulate text based on the solutions available in the VB.NET code and using REGEX (regular expressions) then you are in the right place. In the topic below, you will learn how to easily extract data from any part of the search text using various methods. The topic includes solution examples with descriptions and graphics to better understand the topic for functions such as: Split Substring Left Right R…

cheers

Happy learning

[Shailesh123 (Shailesh Pathak)]

@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

[pattyricarte (Patty Boy)]

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

Happy learning

[sarathi125 (Parthasarathi)]

@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))

[hacky (NotHacker)]

@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

[David3 (David Rajkumar)]

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…!

[Mohansadaiyapillai (Mohanraj S)]

Split&.zip (24.4 KB)

I have created a workflow based on your requirement.