How to Find / Separate a Number from Message Text

[Paul.DeVito]

Is there a way to find and seperate a number from message text? These are all errors within the system. Ultimately I would like to identify material numbers that are generating multiple errors.

Below are several examples. In some cases, the number is not completely seperated (adjoined to /, :, alpha-numeric).

Message Text: Material 5-1617353-7 in plant 0920 has no BOM
Number: 5-1617353-7

Message Text: Cost estimate for material 3-1616980-4/ plant 0919 is incorrect
Number: 3-1616980-4

Message Text: Material CA7934-000 in plant 4110 has material status B5
Number: CA7934-000

 

[jai9]

Hi,

Auuming that the material number is 10 or 11 digits long and always appears at the end as shown in the example,
[TABLE="width: 255"]
<TBODY>[TR]
[TD]Message Text : Material 5-1617353-7 in plant 0920 has no BOM number: 5-1617353-7</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]

=TRIM(RIGHT(A32,11))

Jai

 

[Paul.DeVito]

Hello Jai - Thank you for your response. Your formula may be useful for a couple of the fields, unfortunately the majority contain the part number somewhere within the text. In all examples, the Message Text is what I currently have and the Number is what is the piece of information I would like to end with.

The part number will always be between 8 and 11 characters and will always end in a number (never a letter or symbol).

 

[jai9]

Hi,

I tried to work on this and came up with this.

=TRIM(MID(A32,FIND("Material",A32)+LEN("Material"),12))

The function assumes that part no comes after the word Material in the text. I have taken length of the part as 12 as I was not able to write a code for it. So this may not work for parts whose length is 8,9.

Jai