Help extracting text from within text.

Paul at GTS

Board Regular
Joined
May 17, 2004
Messages
56
Office Version
  1. 365
Platform
  1. Windows
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 ?
 
Last edited:

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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.
 
Last edited:
Upvote 0
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"))))
 
Upvote 0
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.
 
Upvote 0
Thanks all, task all sorted.

Interesting to see different ways of doing same task.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top