Extracting part number from number string

[ruub7]

I think that I am stuck on a problem that should be very easy to solve...

I have made a pivot table to extract certain parts/part numbers from a lot of assemblies.

After that I extracted the seperate parts that I needed from the pivot table in new columns seperated per type of part.
Then I added them together in the column next to it and filtered that column for unique combinaties of those parts.

The problem that I am now stuck with though is that I now have a string of numbers that consists of 2 different part numbers. So I want to untangle them.

But how do I do that when the part numbers aren't a constant length? (They can be 8/9 or 11 numbers long, and there might even be more variations that I'm not yet aware of).
All I know is that the second part numbers starts with 250, so it should be able to write a formula to seperate it based on that information I guess...

So can anyone help me with this?

For example:
number string original --> should become these separate numbers (preferably in seperate columns, but if that is done with 2 seperate formula's, that is perfectly fine).
2101305005125017770050 --> 21013050051 & 25017770050
21010850025015940050 --> 210108500 & 25015940050
21000830025015100 --> 210008300 & 25015100

[xlnitwit]

Then I added them together in the column next to it and filtered that column for unique combinaties of those parts.

I would recommend that you add a delimiter at this stage, such as a pipe symbol. You can then use that to split the part numbers back out again later, which seems safer than relying on the first part number not containing '250' in it.

[JohnTopley]

Try

Data in A1 (as TEXT)

in B1

=LEFT($A1,FIND("250",$A1)-1)

in C1

=SUBSTITUTE($A1,$B1,"")

See very good advice in previous post:

[avk]

If your data in "A2"
In "B2"

Please Login or Register  to view this content.

In "C2"

Please Login or Register  to view this content.

[AlKey]

Enter formula in B1 and drag across to C1 and down

Please Login or Register  to view this content.

v	A	B	C
1	2101305005125017770050	21013050051	25017770050
2	21010850025015900000	210108500	25015900000
3	21000830025015100	210008300	25015100

[Roel Jongman]

After that I extracted the seperate parts that I needed from the pivot table in new columns seperated per type of part.
Then I added them together in the column next to it and filtered that column for unique combinaties of those parts.

Instead of finding formulas why not include the two first columns in your "remove duplicates"
if you select multiple columns you can specify which column must have unique values.

[ruub7]

Thnx nitwit .
That is a good idea, will do that!