Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need Help pulling specific info from a cell

Status
Not open for further replies.

quig699

Technical User
Nov 7, 2006
42
US
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
 
Amy said:
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" [ponder]

BTW - is VBA solution OK?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
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
 
If you drop this code into where you have any other Macro:

Code:
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:

Amy_l2uhfe.png

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

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
Amy said:
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]

Amy_rwhsqw.png


Amy1_utob9v.png


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
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!!![smile]

Thanks,

Amy
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top