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

[Thableaus]

Hi @oneillp111 

You could use REGEX. 

Something like this might work

REGEX_Replace([Part Number], "(.*?)[a-z].*", "$1")

This would capture everything until it reaches the first letter.

Cheers,

[JosephSerpis]

Hi @oneillp111 try using regex I mocked up an example that only extracts numbers.

[oneillp111]

Thanks for the reply, I used Regex before, however some part numbers contain letters so those would be missed.

[RobertOdera]

Hi, @oneillp111 

Try this, if you're open to brute force.

Please mark as an acceptable solution + like, if it satisfies your use case.

The workflow is attached.

Cheers!

[oneillp111]

Thank you, unfortunately it will not always be Part Number at the end, could be anything, it could be nothing. 

[RobertOdera]

Ah, got it @oneillp111 

Question.

Will the actual part number always end in a number or can it also end in a text?

300 400 45678 or

300W45678R

Cheers!

[oneillp111]

It could be a number or a letter

Sent from my iPhone

[MarqueeCrew · Accepted solution]

Let's try:

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

 how about this?

Cheers,

 mark

[oneillp111]

@MarqueeCrew , that seems to work.  Can you explain the reg expression used? I sometimes get lost using the complex reg expressions.