Separating the string

Ranju78

New Member
Joined
Apr 27, 2018
Messages
15
I have a column where customer can input the Manufacturer and model number like
Goodman FGTRD-25418
Phillip HGYTFD25148jnh
HYGFN84K
YTR5245F Samsung
Sonic

<tbody>
</tbody>

Now I have another two column where I have to put Manufacturer and model number like below
How do i do this ..
Manufacturer model number
GoodmanFGTRD-25418
PhillipHGYTFD25148jnh
HYGFN84K
Samsung YTR5245F
Sonic

<tbody>
</tbody>


Please help
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Diffy

Well-known Member
Joined
Dec 22, 2006
Messages
512
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,569
Office Version
  1. 2010
Platform
  1. Windows
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)...
Code:
[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

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
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

New Member
Joined
Apr 27, 2018
Messages
15
Re: Need help with separating the string

Thank you for the solution..
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,569
Office Version
  1. 2010
Platform
  1. Windows
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,118,415
Messages
5,571,973
Members
412,430
Latest member
Huuktkt
Top