How to extract numbers from a cell when the position and length of the numbers are always different

Christin F.

I've tried splitting text with numbers in a string in a single cell but can't find a working formula.

An Item number is always available in a Item/Document Name string, but only the article Number should be copied into a separate column. The position and the amount of the number is variable. The only factor separating them is either a space or an underline.

Paul Newcome

Unfortunately this is going to have to be manual entry because there is not enough consistency. I would initially say we could use a space or underscore, but the two problems with that are that there can be a variable number of underscores before the piece to be pulled, or in the example of rows 4 - 6 there is nothing before the piece to be pulled.

Christin F.

Hi Paul, thank you for your comment, Ok I need to find a work around. THX a lot

Paul Newcome

If you are able to gain more consistency in the document names, then we would certainly be able to use a formula.

Andrée Starå

Hi @Christin F.

I hope you're well and safe!

How many different "patterns" can there be?

We might be able to have different formulas for different patterns. Maybe counting the number of characters or something similar.

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!