Split a column that has multiple spaces between two strings

Help Studio
1

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

image
image740×430 12.2 KB

Thank you,
pari

2

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
3

@parimala.prata

Check attachment, It will split with 2 or more spaces

BlankProcess12.zip (21.8 KB)

Hope this helps

Thanks

1 Like
4

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

5

@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

6

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

7

@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

8

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)

9

@parimala.prata

Check attached

BlankProcess12.zip (24.9 KB)

Mark as solution, if this solves your issue

Thanks

10

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?

11

@parimala.prata

check attached

BlankProcess12.zip (27.4 KB)

Mark as solution if this resolves your issue

Thanks

12

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

2 Likes
13

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