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 ?

 

[Joe4]

Is there any rhyme of reason in determining how many characters should be pulled off?
I am failing to see a consistent pattern.
We need to know the rules, before we can attempt to create anything to do it,

 

[Scott T]

There does not look to be a pattern to the part numbers. You would need the part numbers to be the first x characters or everything before the "-". How do you know what is the part number?

 

[JoeMo]

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 ?

Why does the expected result highlighted in red font not terminate after the D to be consistent with the other expected results shown?

 

[Paul at GTS]

Guys you have hit the nail on the head !

We have no consistance due to the data set being created over a few decades.

As I see it my extract selection would be to extract all text from the right as far as either:-
the first letter but not including that letter
or
untill first character after a space.

Any help ?

 

[Marcelo Branco]

Though one.
In the examples you have shown in all cases the last digit of the result is a letter, less in this:
004D1 0350 0300 -> 004D1
Is this right?

Edit: as already pointed out by JoeMo

M.

 

[Rick Rothstein]

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

Here is a UDF (user defined function) which returns the values you want from the data you showed us. Without knowing how varied your actual 11,000 part numbers truly are, there is no way of knowing if this UDF will work for all of them.

Function PartNum(S As String) As String
  Dim X As Long, Parts() As String
  Parts = Split(S)
  If Not Parts(UBound(Parts)) Like "*[!0-9]*" Then Parts(UBound(Parts)) = ""
  PartNum = Trim(Join(Parts))
  For X = Len(PartNum) To 1 Step -1
    If Mid(PartNum, X, 1) Like "[!0-9]" Then
      PartNum = Trim(Left(PartNum, X))
      Exit For
    End If
  Next
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use PartNum just like it was a built-in Excel function. For example,

=PartNum(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[István Hirsch]

Try this:

Excel Workbook
	A	B
1
2	063G4F1000 0300	063G4F
3	004EWL0450 0550	004EWL
4	004D1 0350 0300	004D
5	004D1D0350 0300	004D1D
6	002 AB20242408	002 AB
7	002 DGPS50	002 DGPS
Sheet1

 

[Rick Rothstein]

Try this:

Sheet1

	A	B
063G4F1000 0300	063G4F
004EWL0450 0550	004EWL
004D1 0350 0300	004D
004D1D0350 0300	004D1D
002 AB20242408	002 AB
002 DGPS50	002 DGPS

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

</tbody>

The value I highlighted in red above is not correct (look at what the OP posted).

 

[Paul at GTS]

Thanks Guys , all good.

Rick, you are correct in above post, as I only had a few to change its not a big issue.

Thanks again.