Need Help pulling specific info from a cell

[quig699]

Hi,

I have been trying to find something on google to help but for the life of me can't track down anything. I have data in this format in a cell.

0101-6,0110-6,0101-6,0110-6,0181-1,0182-1

The data represents SKUs and the quantity our customer ordered. Depending on the order it could just list one sku or as many as the customer ordered. I am looking for a way to have excel find orders with a specific sku in it and then return the number after the dash and ignoring anything else in the cell. Is that possible?

Thanks

Amy

Thanks,

Amy

 

[Andrzejek]

find orders with a specific sku in it and then return the number after the dash and ignoring anything else in the cell

You need to be more specific.

Let's say you have this:[pre]
A B
Amy 0101-6,[highlight #FCE94F]0110[/highlight]-6,0101-6,[highlight #FCE94F]0110[/highlight]-6,0181-1,0182-1
Andy 0102-7,[highlight #FCE94F]0110[/highlight]-7,0103-5,[highlight #FCE94F]0110[/highlight]-4,0182-7,0185-3
etc...[/pre]

and you are looking for sku 0110
How and where do you want to "return the number"

BTW - is VBA solution OK?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[quig699]

Hi Andy,

Thanks for helping. I was thinking of just adding a column and putting the quantity ordered there if I could find a formula to do it.

As far as VBA, I have never used it before, but if it gets me what I need I will try.

Thanks again


Thanks,

Amy

 

[Andrzejek]

If you drop this code into where you have any other Macro:

Public Function Amy(ByRef sku As String, ByRef str As String) As Integer
Dim ary() As String
Dim X As Integer
Dim intCount As Integer

ary = Split(str, ",")
For X = LBound(ary) To UBound(ary)
    If sku = Split(ary(X), "-")(0) Then
        intCount = intCount + Split(ary(X), "-")(1)
    End If
Next X

Amy = intCount

End Function

You can use it (your own function named Amy) as any other Function/Formula in Excel:

As you can see, in cell D1 I have a SKU I want the Count of

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

I have never used it [VBA] before, but if it gets me what I need I will try.

in Excel, hit [tt][red]Alt-F11[/red][/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[quig699]

Andy,

Thank you so much for taking the time to share this with me. It is very much appreciated. You are AWESOME!! It worked first time out.

Have a great day!!!

Thanks,

Amy

 

[remeng]

You might also want to look at the function textsplit() that can be used in your formula bar. There are several new functions added to Excel 365 that can also be useful. Here is a video of some of the new ones.

Link