Split a column that has multiple spaces between two strings

Hi all,

my sample data looks like below: I am trying to split the text in column “Vendor” and move the vendor name into a separate column as shown below: The problem is it is not just one space that separates these two texts . it is dynamic.

I am using the code below and it is working only for some and not for others. Could anyone help please

image
image

Thank you,
pari

If that vendor “id” is always 6 chars long, you can do like this:

String temp = Row("vendor")
Row("vendor") = temp.Substring(0,6)
Row("vendor name") =  temp.Replace(temp.Substring(0,6), "").Trim
1 Like

@parimala.prata

Check attachment, It will split with 2 or more spaces

BlankProcess12.zip (21.8 KB)

Hope this helps

Thanks

1 Like

it is not always 6 characters long. it varies from vendor to vendor

@parimala.prata

Check the attachment attached, It will work even it has dynamic spacing 2 or more spaces

Mark as solution if it works

Thanks

I am using a datatable do modify the data. i modified your formula as shown below: The vendor name column is getting populated with system.string.

Not sure where the mistake is. could you please point out

system.Text.RegularExpressions.Regex.Split(row("Vendor ").ToString, “\s{2,}”)
image

@parimala.prata

Assign a variable type string and then you can fine to go
Eg:
Varaiable name : Vendor
tmpVendor = row(“Vendor”).Tostring

Vendorsplit should be Array of string

Vendorsplit = System.Text.RegularExpressions.Regex.Split(tmpVendor, “\s{2,}”)

Vendor should be string
Vendor = Vendorsplit(1).ToString

If confusing share your excel, I will try to replicate

Hope this helps

Thanks

i am trying and i am getting lost where i have to loop through all the rows
I made up some data. Could you please help
.CP.xlsx (8.3 KB)

@parimala.prata

Check attached

BlankProcess12.zip (24.9 KB)

Mark as solution, if this solves your issue

Thanks

i followed until you split the name and stored it in the variable ‘vendor’. this works for the message box to display the trimmed string. But how do i copy it to the database column.

I am getting the error when i try to copy the value of the variable ‘Vendor’ to a data column. any ideas?

@parimala.prata

check attached

BlankProcess12.zip (27.4 KB)

Mark as solution if this resolves your issue

Thanks

hi bcorrea,

this worked like a charm. even though the vendor code is not always 6 characters long, it still works. If the vendor code is less than 6 characters, it will leave some spaces after the code and i am ok with that.

Thank you,
pari

1 Like

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