Trying to isolate part number with Left and Find String

[oneillp111]

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], ' '))

[MarqueeCrew · Accepted solution]

@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. 

everything before it, if letters, numbers or spaces is what we're after. 

cheers,

 mark

[oneillp111]

@MarqueeCrew , 

I ran into a problem with this and hoping you can help.

Here is the data to parse

7741254698 CA9 4R

I need 7741254698 but I get 774125469894.  I know how to keep it to the length of 10, however that is not always the case, sometimes the first number is 11 digits and sometimes it is less than 10.  Any Ideas 🙂

[MarqueeCrew · Accepted solution]

@oneillp111 

Getword([data],0)

 cheers,

 mark