# How to split multiple value in a cell to multiple cells?

Dear Expert

I need to input some value from Excel A to Excel B, the mapping relationship is as below:

Excel B(Doc Date)->Excel A(Date)
Excel B(Assignment & Reference)->Excel A(Assignment)
Excel B(Amount)->Excel A(Amount)
Excel B(Text & Bank Text)->Excel A(Text)
Excel B(Doc No)->Excel A(Invoice Number)
Excel B(Balance Account)->Excel A(Balance Account)
Excel B(Balance Amount)->Excel A(Balance Amount)
Excel B(Bank Charge Account)->Excel A(Bank Num)
Excel B(Bank Charge Amount)->Excel A(Bank Amount)
Excel B(Tax Code)->Excel A(Tax)

The challenge is that there are multiple Doc No in one Assignment in Excel A.

When it happens, we need to split these multiple Doc No to different cells in the same column one by one

On the other hand, the other value should be only typed into first row of these different cells under the same Assignment. The rest rows of this assignment(which has multiple Docs No.) must be empty

May I know how to make it happen? I put 2 excel files in attachment for your reference.
A.xlsx (10.8 KB)
B.xlsx (10.9 KB)

Excel A:

Excel B:

1 Like

Hi @yangyq10 ,

In order to map the data you would be making use of âCopy/Paste Rangeâ Activity.

I would Suggest you to use read cell and Write cell.

Read Cell, To read the doc number from the cell probably in loop, The output will be stored in a String Variable(TestString).

Have a String array and split the string variable (TestString) using the split method based on newline in case if you have the data like

123465678
908765342

After splitting this would become {â123465678â,â908765342â}

Post the initialization of the string array in the above method you can check the length of the array if it is more than 1 - loop the array to write data in the first row of the column in corresponding sheet using the âWrite Rangeâ activity.

Hope this suggestion helps you out.

Happy Automating.!!

Thanks,
Gautham.

@Gautham_Pattabiraman

Thank you for your feedback. Could you help upload a xaml file for me to check when you have time? I am a beginner and not quite familiar with split and array

And I donât know how to put the rest value only in the 1st row of these invoice number

Sorry for the inconvenience

Dear Experts, may I have your help and upload a workflow for me to study

I have attached the workflow for your Excel A query i.e

1. Split multiple invoice to different cell in the same column &
2. Other values should be only typed into first row else will be empty.

Now, Just copy the value from Excel A to Excel B according to your requirement.

Input Excel A:

After:

Main.zip (3.3 KB)

Hope it helps!!
Happy Automation

Thank you for the detail guidance

I only change the last activity to write DT2 into my targeted excel File B
Then it turn to below result.
Can you help further check on this

RA.7z (134.0 KB)

I have cross-checked from my side its working fine.

Please check the logic you are using to mapping and writing in the Excel B.

Thanks,

Its so weird.

This time I change nothing but just run it
The only change is to change the path of file A
And the result is same as belowâŚ

I have some questions while I study your workflow:

1. Why count=0 means there are multiple invoice number in one cell?

2. The final output in Excel B with different column name in Excel. I try to change it in multiple assign but it just turn out error. May I know how to change the column name in the final output?

3. What is use of new data row here?

4. What is use of Add Data Row here?

I ask my colleague to run same file and it works on his PC

The only difference between us is UiPath version.

My Version is 2023.4 and his 2023.8

Do you think below VB code only works on newer version?

1 Like

@yangyq10

You are creating Excel B or its a predefined template with the Column Name, you just need to add the details from Excel A to Excel B by mapping with column name ?

1. Count=0: For checking the no. of invoice in one cell and to identify that 1st row is completed, So for next row only Invoice number i need to add and other will be empty.
2. newDataRow(âExcelBColumnNameâ) = CurrentRow(âExcelAColumnNameâ)
3. If multiple invoice in once cell, then to split it and add in a new row.
4. For Adding all values in Final DataTable.

Hope itâll helps you to Understand better !!

@yangyq10

You can try this also.

`Split(CurrentRow("Invoice Number").ToString,Environment.NewLine)`

Thanks

It also works in below VB code:

The content in Excel B is same as Excel A. But column name in Excel B is different in Excel A

I try to change column name in Excel B to TaxA from Tax in Excel A
But there is error pop up, do you know why?

I have a predefined template with column name in Excel B

@yangyq10

So if you have the template then instead of using Clone, Just Read the Excel B also as DT2

Hope it helps!!

After I use read excel B as DT2, I try below 3 ways to split the Invoice Number in Excel A but none of them works.

Can you help take a look which part is incorrect?

RA.7z (137.6 KB)

CurrentRow(âInvoice Numberâ).ToString().Split(Chr(13) & Chr(10))

Split(CurrentRow(âInvoice Numberâ).ToString,Environment.NewLine)

CurrentRow(âInvoice Numberâ).ToString().Split({Environment.NewLine},StringSplitOptions.RemoveEmptyEntries)

@yangyq10

Try this:

UiPath_Forum.zip (22.3 KB)

Thanks

1 Like

@Nawazish_Ahmad Thank you for your patience and strong support. It works! I learn a lot from you and this process. Have a nice weekend

1 Like

Hi @yangyq10 ,

One solution giving with Linq Query, you may try once. am splitting the rows using it.

``````(From row In dtInput.AsEnumerable
Let arrValue = row("Invoice Number").ToString.Split(Environment.NewLine.ToCharArray,StringSplitOptions.RemoveEmptyEntries).ToList
From value In arrValue
Let index = arrValue.IndexOf(value)
Let replacedRow = row.ItemArray.TakeWhile(Function(item) Not item.ToString.Contains(value)).Concat({value}).Concat(row.ItemArray.SkipWhile(Function(item) Not item.ToString.Contains(value)).Skip(1))
Let newRow = row.ItemArray.Select(Function(item) If( item.ToString.Contains(value),value,"" ))