Removing a number within a string of text

[IanG]

Hi,

We want to remove every instance where there is a _400 packs from a string of text.

So in the example below I want all of the 41S7_400, & 41S8_400, & 41S10_400, removed

41S7_1,41S7_2,41S7_5,41S7_10,41S7_20,41S7_30,41S7_50,41S7_100,41S7_200,41S7_300,41S7_400,41S7_500,41S7_1000,41S8_1,41S8_2,41S8_5,41S8_10,41S8_20,41S8_30,41S8_50,41S8_100,41S8_200,41S8_300,41S8_400,41S8_500,41S8_1000,41S10_1,41S10_2,41S10_5,41S10_10,41S10_20,41S10_30,41S10_50,41S10_100,41S10_200,41S10_300,41S10_400,41S10_500,41S10_1000,

So it ends up looking like this

41S7_1,41S7_2,41S7_5,41S7_10,41S7_20,41S7_30,41S7_50,41S7_100,41S7_200,41S7_300,41S7_500,41S7_1000,41S8_1,41S8_2,41S8_5,41S8_10,41S8_20,41S8_30,41S8_50,41S8_100,41S8_200,41S8_300,41S8_500,41S8_1000,41S10_1,41S10_2,41S10_5,41S10_10,41S10_20,41S10_30,41S10_50,41S10_100,41S10_200,41S10_300,41S10_500,41S10_1000,

Possible?

 

[shrivallabha]

One way
1. Open FIND and REPLACE dialog (CTRL+H).
2. Find What:=,????_400,
3. Replace with:= ,
4. Press Replace

Repeat the same with ,?????_400,

Hth

Had to edit after posting as it turns out that the string before underscore varies in length.

 

[IanG]

Thanks, I now realise I should have explained that I cant use find and replace because the part number eg 41S10_400, eg: first part of the number is our part number = 41S10 is our part number, and the 2nd part = 400 is the pack qty. This number changes for every part and pack qty we have.
The sheet I am working on has 56,000 part numbers.
The example above is where i just want the 400 packs removed, in other cases I may want the 300 and 400 packs removed. In other cases 200, 300 and 400 packs required
Therefore I am after a formula where I can specify the qty or qty's eg within a range, remove every part number that has a _400

Hope this makes sense

 

[azumi]

You can use SUBSTITUTE function like this

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"41S7_400,",","),"41S8_400,",","),"41S10_400,",",")

 

[IanG]

One way
1. Open FIND and REPLACE dialog (CTRL+H).
2. Find What:=,????_400,
3. Replace with:= ,
4. Press Replace

Repeat the same with ,?????_400,

Hth

Had to edit after posting as it turns out that the string before underscore varies in length.

Hi
I found using this other data in another sheet changed. Below is all the data in one cell, my OP only showed a small section.
When I did a replace based on _400, other data changed. at the bottom in red is an example, the 3000 pack changed to 30000

73AS62_2000,73AS62_3000,73AS62_4000,73AS62_5000,73AS62_10000,73AS63_50,73AS63_100,73AS63_200,73AS63_300,73AS63_400,73AS63_500,73AS63_1000,73AS63_2000,73AS63_3000,73AS63_4000,73AS63_5000,73AS63_10000,73AS64_50,73AS64_100,73AS64_200,73AS64_300,73AS64_400,73AS64_500,73AS64_1000,73AS64_2000,73AS64_3000,73AS64_4000,73AS64_5000,73AS64_10000,73AS65_50,73AS65_100,73AS65_200,73AS65_300,73AS65_400,73AS65_500,73AS65_1000,73AS65_2000,73AS65_3000,73AS65_4000,73AS65_5000,73AS65_10000,73AS66_50,73AS66_100,73AS66_200,73AS66_300,73AS66_400,73AS66_500,73AS66_1000,73AS66_2000,73AS66_3000,73AS66_4000,73AS66_5000,73AS66_10000,73AS68_50,73AS68_100,73AS68_200,73AS68_300,73AS68_400,73AS68_500,73AS68_1000,73AS68_2000,73AS68_3000,73AS68_4000,73AS68_5000,73AS68_10000,73AS610_50,73AS610_100,73AS610_200,73AS610_300,73AS610_400,73AS610_500,73AS610_1000,73AS610_2000,73AS610_3000,73AS610_4000,73AS610_5000,73AS610_10000,73AS612_50,73AS612_100,73AS612_200,73AS612_300,73AS612_400,73AS612_500,73AS612_1000,73AS612_2000,73AS612_3000,73AS612_4000,73AS612_5000,73AS612_10000,73AS614_50,73AS614_100,73AS614_200,73AS614_300,73AS614_400,73AS614_500,73AS614_1000,73AS614_2000,73AS614_3000,73AS614_4000,73AS614_5000,73AS614_10000,73AS616_50,73AS616_100,73AS616_200,73AS616_300,73AS616_400,73AS616_500,73AS616_1000,73AS616_2000,73AS616_3000,73AS616_4000,73AS616_5000,73AS616_10000,73AS620_50,73AS620_100,73AS620_200,73AS620_300,73AS620_400,73AS620_500,73AS620_1000,73AS620_2000,73AS620_3000,73AS620_4000,73AS620_5000,73AS622_50,73AS622_100,73AS622_200,73AS622_300,73AS622_400,73AS622_500,73AS622_1000,73AS622_2000,73AS622_3000,73AS622_4000,73AS82_50,73AS82_100,73AS82_200,73AS82_300,73AS82_400,73AS82_500,73AS82_1000,73AS82_2000,73AS82_3000,73AS82_4000,73AS82_5000,73AS82_10000,73AS84_50,73AS84_100,73AS84_200,73AS84_300,73AS84_400,73AS84_500,73AS84_1000,73AS84_2000,73AS84_3000,73AS84_4000,73AS84_5000,73AS84_10000,73AS86_50,73AS86_100,73AS86_200,73AS86_300,73AS86_400,73AS86_500,73AS86_1000,73AS86_2000,73AS86_3000,73AS86_4000,73AS86_5000,73AS86_10000,73AS88_50,73AS88_100,73AS88_200,73AS88_300,73AS88_400,73AS88_500,73AS88_1000,73AS88_2000,73AS88_3000,73AS88_4000,73AS88_5000,73AS88_10000,73AS810_50,73AS810_100,73AS810_200,73AS810_300,73AS810_400,73AS810_500,73AS810_1000,73AS810_2000,73AS810_3000,73AS810_4000,73AS810_5000,73AS810_10000,73AS812_50,73AS812_100,73AS812_200,73AS812_300,73AS812_400,73AS812_500,73AS812_1000,73AS812_2000,73AS812_3000,73AS812_4000,73AS812_5000,73AS814_50,73AS814_100,73AS814_200,73AS814_300,73AS814_400,73AS814_500,73AS814_1000,73AS814_2000,73AS814_3000,73AS814_4000,73AS814_5000,73AS816_50,73AS816_100,73AS816_200,73AS816_300,73AS816_400,73AS816_500,73AS816_1000,73AS816_2000,73AS816_3000,73AS816_4000,73AS816_5000

73AS816_30000,73AS816_5000

 

[IanG]

You can use SUBSTITUTE function like this

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"41S7_400,",","),"41S8_400,",","),"41S10_400,",",")

Thanks for this, unfortunately it took too many steps as I had to get the data I want to remove from another sheet, join it, create the formula etc. On some sheets there are 3000 lines

 

[shrivallabha]

See if attached file is of any help.

It contains one User Defined Function (UDF)

Public Function ReplaceParts(srcRange As Range, arInputQuantity As Variant) As String
Dim RgEx As RegExp: Set RgEx = CreateObject("VBScript.RegExp")
Dim i As Long
ReplaceParts = srcRange.Value
For i = LBound(arInputQuantity) To UBound(arInputQuantity)
    With RgEx
        .MultiLine = True
        .Global = True
        .Pattern = "[A-Za-z0-9]+_" & CStr(arInputQuantity(i)) & ","
        ReplaceParts = .Replace(ReplaceParts, "")
    End With
Next i
End Function

Currently it is shown for quantity 400
=ReplaceParts(A2,{"400"})

The function can be used to remove multiple cases as well. It will be like:
=ReplaceParts(A2,{"300","400","1000"})

 

[r2c2]

Thanks for this, unfortunately it took too many steps as I had to get the data I want to remove from another sheet, join it, create the formula etc. On some sheets there are 3000 lines

If you are using Excel 2016 or Office 365, why not use Power Query to do this for you. You can easily setup a query to read part numbers, ignore quantities from another list and create new output (either comma delimited as you have or some other format). Here is an example of how to do this.

Of course, you can also use VBA or formulas, but this is the right job for PQ.

 

[IanG]

Thanks for responses everyone, unfortunately I don't have the skills to implement PQ or VBA, I was hoping a formula which is my skill level be available.