Help extracting text from within text.

[Paul at GTS]

Hi all,
My problem is I have a set of part numbers as below:
063G4F1000 0300
004EWL0450 0550
004D1 0350 0300
004D1D0350 0300
002 AB20242408
002 DGPS50

What I need to do is extract the first part of the number to achieve
063G4F
004EWL
004D1
004D1D
002 AB
002 DGPS

I have around 11000 parts to sort in this way.

Have tried to find solution with LEN, LEFT, MID & FIND but cant get it working. Perhaps VBA has an answer.

Hope that make sence ?

 

[István Hirsch]

Then I am afraid I don't understand the rule.

 

[Rick Rothstein]

Then I am afraid I don't understand the rule.

The underlying algorithm that I used in the UDF that I posted in Message #7 (which works correctly for the data that was posted) was this...

1) If the text after the last space is all digits, delete them.

2) Work backwards from what is left, character by character until you find a non digit (spaces are considered non-digits).

3) Return everything to the left of that non-digit, but first apply the Trim function to it in order to remove trailing spaces in case that was the non-digit that was found.

 

[István Hirsch]

Thanks, Rick. The modified version to be confirmed with Ctrl + Shift + Enter (not just Enter):

=LEFT(A2,IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=2,FIND(" ",A2),MATCH(2,1/--(MID(A2,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2))),1)>="A"))))

 

[Rick Rothstein]

Thanks, Rick. The modified version to be confirmed with Ctrl + Shift + Enter (not just Enter):

=LEFT(A2,IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=2,FIND(" ",A2),MATCH(2,1/--(MID(A2,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2))),1)>="A"))))

You are welcome István. You formula is a lot shorter than I thought it would have to be. I only have one concern with it and that is the OP did not give us a truly representative sample of his data. My concern would be with a value like this...

002 AB2 0242408

where I would think, based on his other examples, that the answer would be this...

002 AB2

and not 002 which your formula returns. That his data could look like that is just a hunch on my part, but I thought I would mention it so the OP would be alert for it just in case. But still, good job for handling the examples as presented.

 

[Paul at GTS]

Thanks all, task all sorted.

Interesting to see different ways of doing same task.