Trying to isolate part number with Left and Find String

I have a workflow that isolates part numbers from text files and it works 95% of time without issue. What I am running into is when the part number format changes and someone adds spaces instead of dashes or nothing at all.

I use the following to isolate the data in The first image. This removes the name of the part, leaving the part number only as seen in the second image for row 2. However Row 1 obviously cuts at the first space. Is there another way to do this that I am unaware or? I cant remove the spaces because some part numbers have letters in them and the the part numbers are different lengths

Left([Part Number1], FindString([Part Number1], ' '))

start.png

End.png

Tips and Tricks

Best Practices

Accepted answers

MarqueeCrew

Let's try:

Trim(regex_replace([text field],"([0-9A-Za-z\s])\u*\s*\u*",'$1'))

how about this?

Cheers,

mark

MarqueeCrew

@oneillp111 ,

would you believe my dog jumped on the keyboard and created it?

Trim(regex_replace([text field],"([0-9A-Za-z\s])\u*\s*\u*",'$1'))

the outer trim removes space from the beginning and end I if the regular expression replacement.

the \u*\s*\u* is the trick. We're looking for a single word or two words. \s* means that there could be zero or many spaces between one it two words at the end of the text.