Extract Stock numbers on to a seperate Sheet excluding those numbers starting with a "U"

[Howardc1001]

I have data on sheet "Data"

I need to extract the data to sheet "Extract" using advanced Filter , where the stock numbers in Col C do not start with a U for E.G. UR31585


I have set up the criteria in Col U on sheet "Extract" but all the data is extracted to sheet "Extract"

I have manually extracted the data to sheet "extract" so you can see what I am trying to achieve


It would be appreciated if someone can amend my criteria in Col "U" as this apppears to be set up incorrectly

[Gregb11]

One option:

=FILTER(Data!A2:D9,LEFT(Data!C2:C9,1)<>"U")

Or are you saying you want to enter any letter in U2? If so, put just "U" (without quotes) in U2, then use this:

=FILTER(Data!A2:D9,LEFT(Data!C2:C9,1)<>U2)

If, however, you're wanting to put in "<>U*" or "=U", etc., don't think you can do that. You can use it as a variable for a value, but I don't think you can include the <> or =.

[Howardc1001]

Thanks Gregb11


Thanks for your Formula. However , I get #Spill in A2

Please check and correct

[Gregb11]

Yes, that formula "SPILLS" into the other cells, so you need to delete all the other values in those cells.
I've attached a copy.

[sandy666]

with Power Query

BR Code	Date	Stock Number	Branch
998714	24/02/2023	BR13158	BR1
998714	24/02/2023	BR31592	BR1
998714	24/02/2023	BR31586	BR1
998715	24/02/2023	BR31601	BR1
998715	24/02/2023	BR31572	BR1
998715	24/02/2023	BR31541	BR1

you can define which characters (from the beginning) you want to exclude, eg. U or UR and so on...

Please Login or Register  to view this content.

[Howardc1001]

Thanks GregB11

Now I know to clear the data with the filter formula first

[Howardc1001]

Thanks Sandy

[sandy666]

You are welcome