Parse a string before last "space" before a period

[Dan91]

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.

[Thableaus]

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+\..*)

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]

He @Dan91 ,

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

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

See tool configuration below:

[Dan91]

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]

@Dan91 

What about this?

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

Cheers,

[Dan91]

@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 · Accepted solution]

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

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

Cheers,