community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Parse a string before last "space" before a period

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.

Alteryx Certified Partner
Alteryx Certified Partner

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,

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

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.

Alteryx Certified Partner
Alteryx Certified Partner

@Dan91 

 

What about this?

 

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

 

Cheers,

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;

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

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

 

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

 

Cheers,

Labels