Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Parse a string before last "space" before a period

Dan91
5 - Atom

Hi everyone,

 

I'm new to Alteryx so I hope you can help me with a quick solution on the following matter.

 

I do have a string where the first part includes the "Item number" (could include letters, numbers and/or spaces) and then 7 different numbers each of them representing various attributes of the item.   (e.g. 9BD 3/8MF X MF 14.0000 913.38 38.99 1.74 0.00 0.84 952.73 -> where "9BD 3/8MF X MF is the item name").

 

Does anyone have any idea how can I parse the "item name" as defined above in a single column. (using text to columns is not helpful as the "item name" could include spaces as well). I narrowed down the pb to the following: the "item name" consists in the set of characters before the last space before the first period.

 

Thanks so much.

6 REPLIES 6
Thableaus
17 - Castor
17 - Castor

Hi @Dan91 

 

Using REGEX tool with Parse Method, this could work.

EDIT: Just realized you want the item name. So you could use this to split up.

 

(.*?)(\d+\..*)

 

 

clipboard_image_0.png

 

You can them use the Trim function in Formula Tool or the Data Cleansing tool to get rid of possible extra spaces or characters.

 

Cheers,

AmeliaG
Alteryx
Alteryx

He @Dan91 ,

 

Thanks for your question! It is possible to achieve this with Regex. 

 

Regex Expression: (.+?)\s(\d+\..+)

 

See tool configuration below:

 

regex.png

Dan91
5 - Atom

Hi Thableaus,

 

Thanks a lot for the very, very quick response. I was trying this solution and seems perfect for all scenarios except one:  in case the number after the "item name" has a decimal

 

172-04522-00 84,500.0000 8,043.15 322.50 0.00 0.00 0.00 8,385.85 -> for this example it returns 172-04522-00 84, basically including the 84, in the "item name".

 

thanks again.

Thableaus
17 - Castor
17 - Castor

@Dan91 

 

What about this?

 

(.*?)(\d+[,\.].*)

 

Cheers,

Dan91
5 - Atom

@Thableaus,

 

This is more than great. Without wanting to become annoying, my last question: what if the number after the "item number" has a - in front.

 

172-04522-00 -84,500.0000 8,043.15 322.50 0.00 0.00 0.00 8,385.85

 

thanks a lot;

Thableaus
17 - Castor
17 - Castor

No problem. Just add a -? in front of it.

 

(.*?)(-?\d+[,\.].*)

 

Cheers,

Labels