Excel Split Column

I have attached a sample excel file, I’m trying to split the excel column. In the user name column there are names and after the name there is a number that begins with N. I need to take the number that begins with N and split it and add it into the number column while keeping the names in the User Name Column. Any help or workflow would be appreciated.

matchtest.xlsx (10.5 KB)

Hi @NATHAN_MORA

Please find the attached xaml file for your reference. Try this and let me know whether it meets your expectation.

SplitColumn.xaml (10.4 KB)

Feel free to reach us at any time if you have doubts. Thanks.

Happy Automation

Vignesh,
Nice work and thank you, is there a way to keep the N from the number when added into the number column? the number would be n34576 instead of just 34576.the number have a capital N and a lower case n In the
original. I posted the excel sheet with the the numbers having a smaller case n. Thanks again this is going to save me if i get this figured out in the next hour
matchtest.xlsx (10.8 KB) excel sheet some of

Vignesh,
I got it figured out I just edited the split Column assign activity. Thank you for this i will diffidently use it in the future.

Okay @NATHAN_MORA

Even I attached the updated xaml file for your reference.

SplitColumn.xaml (11.2 KB)

Thanks.

Vignesh,attachederror
I have a few more issues, I have attached the file I’m working with. its has many rows and some of the name don’t have the N2345 number and im getting the error when i run it with the attached file. it should just run through each user name and split the string like we did earlier and if there is no number just move to the next one. Any ideas?
new split.xlsx (18.8 KB)

@NATHAN_MORA - Let me give you a regex solution…with Invoke code…Please hang on…

1 Like

@NATHAN_MORA - Here you go…

Invoke Code:

dt.AsEnumerable().ToList().ForEach(Sub(row) row(“Number”)= system.text.RegularExpressions.Regex.match(row(“User Name”).ToString,“N\d+”, regexoptions.IgnoreCase))

  Edit Arguments

My Output

Hope this helps… I purposely did not share the xaml here. Since this has only 3 steps, I let you to try so that you will learn stuff easily…

Could you share a picture of the properties panel for the invoke code activity? i have never used this activity before and my code is still in the learning process

@NATHAN_MORA - Here you go SplitColumn_NM.zip (53.0 KB)

Run this workflow as is, you will see the sheet2 created with the required output…

1 Like

Thank you Vignesh and Prasath17 for the help I appreciate it

2 Likes

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.