Separating the string

[Ranju78]

I have a column where customer can input the Manufacturer and model number like
[TABLE="width: 200"]
<tbody>[TR]
[TD]Goodman FGTRD-25418 [/TD]
[/TR]
[TR]
[TD]Phillip HGYTFD25148jnh [/TD]
[/TR]
[TR]
[TD]HYGFN84K [/TD]
[/TR]
[TR]
[TD]YTR5245F Samsung [/TD]
[/TR]
[TR]
[TD]Sonic [/TD]
[/TR]
</tbody>[/TABLE]

Now I have another two column where I have to put Manufacturer and model number like below
How do i do this ..
[TABLE="width: 200"]
<tbody>[TR]
[TD]Manufacturer [/TD]
[TD]model number [/TD]
[/TR]
[TR]
[TD]Goodman[/TD]
[TD]FGTRD-25418[/TD]
[/TR]
[TR]
[TD]Phillip[/TD]
[TD]HGYTFD25148jnh[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]HYGFN84K[/TD]
[/TR]
[TR]
[TD]Samsung [/TD]
[TD]YTR5245F[/TD]
[/TR]
[TR]
[TD]Sonic [/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Please help

 

[Diffy]

Re: Need help with separating the string

Do you have a unique list of Manufacturers that could be referenced? If not is it easy to create one?

 

[Rick Rothstein]

Re: Need help with separating the string

Assuming your Model Numbers always start with two upper case letters as you examples show, then here is a pair of UDFs (User Defined Functions) that will return the values that you want (the function names tell you which column to place each one in)...

[table="width: 500"]
[tr]
	[td]Function Manufacturer(S As String) As String
  Dim X As Long, UpperLower As Long, TwoUppers As Long
  For X = 1 To Len(S)
    If UpperLower = 0 And Mid(S, X, 2) Like "[A-Z][a-z]" Then UpperLower = X
    If TwoUppers = 0 And Mid(S, X, 2) Like "[A-Z][A-Z]" Then TwoUppers = X
  Next
  If TwoUppers > UpperLower Then
    Manufacturer = Trim(Left(S, TwoUppers - 1))
  Else
    Manufacturer = Trim(Mid(S, UpperLower))
  End If
End Function

Function ModelNumber(S As String) As String
  Dim X As Long, UpperLower As Long, TwoUppers As Long
  For X = 1 To Len(S)
    If UpperLower = 0 And Mid(S, X, 2) Like "[A-Z][a-z]" Then UpperLower = X
    If TwoUppers = 0 And Mid(S, X, 2) Like "[A-Z][A-Z]" Then TwoUppers = X
  Next
  If TwoUppers > UpperLower Then
    ModelNumber = Trim(Mid(S, TwoUppers))
  Else
    ModelNumber = Trim(Left(S, UpperLower - 1))
  End If
End Function[/td]
[/tr]
[/table]

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 Manufacturer and ModelNumber just like it was a built-in Excel function. For example,

=Manufacturer(A1)

=ModelNumber(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.

 

[jtakw]

Re: Need help with separating the string

Hi Rick,

Haven't tested your UDF, but was just curious, would it hold for Manufacturers/Companies like: AT&T, IBM, HP, AOL (is AOL still around?), just some samples....

 

[Ranju78]

Re: Need help with separating the string

Thank you for the solution..

 

[Rick Rothstein]

Re: Need help with separating the string

Haven't tested your UDF, but was just curious, would it hold for Manufacturers/Companies like: AT&T, IBM, HP, AOL (is AOL still around?), just some samples....

No, my UDF would not work for those... nor would anyone else's UDF either as there is nothing solid to go on with them (I'm thinking of the 3M company when I say that when I say that).