Link to home
Start Free TrialLog in
Avatar of RichardAtk
RichardAtk

asked on

Excel Formula to split product code

Hi

In Excel I want to split a product code up based on the second - in a string
Full code in A1 results to b1

ZR-38-GR                ZR-38
ZSR-G19-BE-M       ZR-G19
ZS-H60                    ZS-H60
ZFS-70-SM-XXL      ZFS-70
XTE-24-BL-25         XTE-24
XF-3-5                      XF-3

So it splits it before the second - but in some cases (third line there is no second -  

Thanks
ASKER CERTIFIED SOLUTION
Avatar of Professor J
Professor J

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi,

Please paste below code in your module:
Function FindWord(Source As String, Position As Integer)
Dim arr() As String
arr = VBA.Split(Source, "-") 'Change the string which suits you
xCount = UBound(arr)
If xCount < 1 Or (Position - 1) > xCount Or Position < 0 Then
    FindWord = "-" 'Change the string which suits you
Else
    FindWord = arr(Position - 1)
End If
End Function

Open in new window

Suppose your Alpha Numeric Values starts from A1, then in B1 try this Formula:
=FindWord(A1,1)&"-"&FindWord(A1,2)

Open in new window

See the outcome in below image:
User generated image
Avatar of RichardAtk
RichardAtk

ASKER

Perfect thanks
Place this in C1 and then copy down.

=IF(LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))<2,A1,LEFT(A1,FIND("#",SUBSTITUTE(A1,"-","#",2))-1))

Open in new window

~bp
you are welcome RichardArk. glad i was able to help.