topic RE: Split the text in multiple rows in Product Forum

Split the text in multiple rows

AsitabhaDeb — Wed, 30 Sep 2020 22:39:00 GMT

Hi All,

I would like to split the data from multiple cell at a time. will it be possible .
First 5 alphabet from the text and paste in different column.
From cell# F2 to F100 first 5 alphabet need to split and paste in column G.
Please help ASAP.

BR/Asitabha

------------------------------
Asitabha Deb
------------------------------

RE: Split the text in multiple rows

NileshJadhavBP — Thu, 01 Oct 2020 13:13:00 GMT

Hi,

Yes you can achieve this using below steps.

Create Instance
Open Workbook
Get excel data as collection ( Use collection as its little bit faster compare to excel get value and set value)
Create new column in collection to set value - if column is not there in excel
now you have to use loop to access the collection data.
Inside data you have to use left function to get 5 alphabets and store in collection new column
write collection in excel.

I hope it will solve your issue.

Thanks
Nilesh

------------------------------
Nilesh Jadhav
Senior RPA Specialist
------------------------------

RE: Split the text in multiple rows

AsitabhaDeb — Thu, 01 Oct 2020 14:40:00 GMT

Ok Nilesh. I will try and reply to you.

Thanks

Asitabha

RE: Split the text in multiple rows

AsitabhaDeb — Thu, 01 Oct 2020 15:11:00 GMT

Hi Nilesh,

Can you please explain point no. 6. It will be very helpfull.

BR/DEB

RE: Split the text in multiple rows

NileshJadhavBP — Thu, 01 Oct 2020 16:12:00 GMT

Hi,

Consider your collection name is "Data" and there are 4 column inside your excel file which header is
1st Col = "FName" , 2nd Col = "MName" , 3rd col = "Lname" , 4th Col = "Alphabate"

Now follow below steps

drag and drop one calculation stage and inside write expression Trim(Left([Data.Fname],5))
In store result write [Data.Alphabate]

1st step will extract your first 5 character excluding space and 2nd will store extracted value in 4th Column.
Use write collection in excel action after end your loop.

I hope your issue will resolve 🙂

Do let me know if any issue.

Thanks
Nilesh

------------------------------
Nilesh Jadhav
Senior RPA Specialist
------------------------------

RE: Split the text in multiple rows

AsitabhaDeb — Mon, 05 Oct 2020 15:37:00 GMT

Hi Nilesh,

The above Trim expression not working. Its giving me the error message.

RE: Split the text in multiple rows

NrupalJoshi — Mon, 05 Oct 2020 17:54:00 GMT

I think the trim and Left should be reversed.. so first trim any empty spaces and then use left.. no?
Left(Trim([Data.Fname]),5)

------------------------------
Hetal Rathore
------------------------------

RE: Split the text in multiple rows

AsitabhaDeb — Tue, 06 Oct 2020 14:08:00 GMT

HI,

Unable to solve the issue.

I have data in the collection filed (Result Desc.)

Data = BD58DG896FG

I want the first 5 data i.e BD58D and from cell# F2 to F100. Same type of data

Thanks & Regard,

Asitabha Deb

RE: Split the text in multiple rows

NileshJadhavBP — Tue, 06 Oct 2020 14:33:00 GMT

Hi Asitabha,

What error you are getting?

try to use Left([Data.Fname],5) ( remove trim for time being)

Thanks
Nilesh

------------------------------
Nilesh Jadhav
Senior RPA Specialist
------------------------------

RE: Split the text in multiple rows

ShreeAbhi — Mon, 11 Sep 2023 02:08:00 GMT

I have text file with 200 data .such as name, policy number, account number and etc etc. How I could split it according to the column in my development ?

------------------------------
Shree Abhi
------------------------------

RE: Split the text in multiple rows

Mukeshh_k — Mon, 11 Sep 2023 02:39:00 GMT

Hi Shree,

If you could elaborate, is it comma separated or just the spacing in between the values ? i.e. Name, Policy Number, etc ?

How does it look like ? and if you could create a lookalike text file with few dummy entries in it that would help!

------------------------------

Regards,

Mukesh Kumar - Senior Automation Developer

NHS, England, United Kingdom, GB
------------------------------

RE: Split the text in multiple rows

LeonardoSQueiroz — Mon, 11 Sep 2023 05:29:00 GMT

Hello Shree Abhi !

Does the collection with the desired column pattern already exist or will it reuse the input file header?

------------------------------
Leonardo Soares
RPA Developer
------------------------------

RE: Split the text in multiple rows

DevendranVenkatesh — Mon, 11 Sep 2023 09:29:00 GMT

Please share the sample screen shot of input file for better understanding

------------------------------
Best Regards,
Devendran Venkatesh
RPA - Technical Architect |
Riyadh 11413, Kingdom of Saudi Arabia
Phone. +966-556784162
------------------------------