Parsing string by character limit without breaking mid-word

Hello,

I have a data set with customer names, some of them as long as 200 character lengths. I want to create 3 new columns from the customer name. Column 1 character limit 40, Column 2 character limit 40, Column 3 remaining characters. I've tried using a substring but i'm running into the issue of the columns breaking mid-word.

For Example:

Data Field: I have a data set with customer names, some of them as long as 200 character lengths

Column 1: I have a data set with customer names, (39 characters)

Column 2: some of them as long as 200 character (37 characters)

Column 3: lengths (remaining characters)

Is there a way to break on the delimiter (space) <40 characters?

I would provide a better example of a field i'm actually using but for security purposes I cannot. Any help would be greatly appreciated. I've tried looking into the Regex tool but i haven't been able to wrap my head around it.

Thanks,

Parse

Accepted answers

jdunkerley79

This can be done using a Regex tool in Parse mode.

This expression should work

^(.{0,40}) (.{0,40}) (.*)$

I attach a sample with fake data.

It will fail if names are greater than 40 characters long as the regex can't then break it up.

The attached version will put the whole string in third field if it can't be parsed as first name > 40 chars

Split Words.yxmd

Claje

I found a few issues/inconsistencies with regular expressions for names that had really long segments - at least based on how I interpreted the business rules.

My understanding:

If there is a name in the first or second name which is longer than 40 characters, that whole name should be in the third field.

As such, I built out a workflow (which can probably be optimized more than this is) which will split out the data.

To do this, I split the data into rows using space as a delimiter, and then concatenated these strings together, and calculated the length each step along the way. I then assigned each of these a column number based on overall length, and took the LAST value per record based on column number.

I've attached a workflow for this.

Hope this helps!

claje_splitdata.yxmd

All comments

ZacharyM

Would it work to break on the delimiter (space), and then pare down the columns into the necessary lengths afterwards?

For example;

[50 character first name] [60 character last name] [15 remaining characters] -> Break into three columns based on the space

Then in step two, throw out 10 characters from the First name, 20 from the Last Name, and 12 from the 15 remaining?

jlwhittaker

How would I go about doing that? Apologies, I'm fairly new to Alteryx and watching the tutorials on Regex to see if I can find a solution.

jdunkerley79

This can be done using a Regex tool in Parse mode.

This expression should work

^(.{0,40}) (.{0,40}) (.*)$

I attach a sample with fake data.

It will fail if names are greater than 40 characters long as the regex can't then break it up.

The attached version will put the whole string in third field if it can't be parsed as first name > 40 chars

Split Words.yxmd

Claje

I found a few issues/inconsistencies with regular expressions for names that had really long segments - at least based on how I interpreted the business rules.

My understanding:

If there is a name in the first or second name which is longer than 40 characters, that whole name should be in the third field.

I've attached a workflow for this.

Hope this helps!

claje_splitdata.yxmd

jlwhittaker

Thanks! This worked perfectly.

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

michael_weaver 335

Hollingsworth 335