Parse a string before last "space" before a period

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.

Regex

Accepted answers

Thableaus

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

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

Cheers,

All comments

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.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703