CODEWITHSUNDEEP
exceltextexcel-formula
GeorgeC
GeorgeC

Reputation: 1037

Trying to find excel formula to seperate complex text in excel

I am trying to find the most efficennt way to make the following into a single column

The text appears in 3 different ways, the value after === is what it should become.

LOT: 4 TYP: SP PLN: 262354 === 4SP262354
LOT: 2 RP: 909144 === 2RP909144
LOT: 174 SP: 199682 === 174SP199682

Assuming the text to split is all in column L3 I have the following

M is =MID(L3,5,FIND("TYP",L3)-6) ===  1
N is =MID(L3,FIND("TYP:",L3)+4,FIND("PLN",L3)) ===  SP PLN: 262354
O is =MID(N3,1,FIND(" PLN",N3)-1) === SP
P is =RIGHT(N3,FIND(": ",N3)-2) ===262354

It works for some but not others and not in cases where there is no "TYP".

In Python we can do from start char no to end char no i.e text[1:9] and use find to locate the value for 1 and 9 but mid works with start and number of characters from that point, and so it causes issues.

Upvotes: 1

Views: 44

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520878

Try this formula:

=SUBSTITUTE(
     SUBSTITUTE(
         SUBSTITUTE(
             SUBSTITUTE(
                 SUBSTITUTE(A1, " ", ""), "TYP:", ""), "PLN:", ""), "LOT:", ""), ":", "")

The innermost substitution SUBSTITUTE(A1, " ", "") removes all whitespace. The other replacements remove the following:

TYP:
PLN:
LOT:
:   <-- remove any stray commas

Output:

enter image description here

Of course, you could always handle this in another way. You could use Excel's find a replace functionality from the drop down menu and just do each of these steps separately.

Upvotes: 2

RealCheeseLord
RealCheeseLord

Reputation: 795

So basically you Need to replace LOT:, TYP:, PLN: and : with "" like this:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(L3; ": "; ""); " LOT"; ""); " PLN"; ""); " TYP"; "")

maybe you have to Play around with the spaces a Little, but this should be a good starting Point

Upvotes: 1

Related Questions