Macro needed to split off the last piece of a cell to the next 1 or 2 adjacent cells depending on content

[mellojeff]

Greetings my friends!

I've been disabled for 5 years, part of my disability is short term memory. The reason I tell you this is so you will understand IF this question is redundant. I have looked at the forums and haven't seen the answer I seek. I also may not have described what I need accurately. Please be patient and ask if I am missing information. I'm trying to start a catalog business to help my meager disability income, lol.

I am very grateful for any help that I can get. Thank you in advance.

Now to the problem;

I have a list of inventory which will be updated regularly. I have to "clean up" column B for printing catalogs and need some of the information moved to the next adjacent cells.

I have in the second cell of the document (In this case, cell "B-2") below my heading, the product names, count per, and the majority of them have a weight. For example, it might have, "Snicker bars 25/8oz" I do have a few that have a space after the "8" but not enough to worry about, for those I can manually check them after and adjust accordingly. The description in column "B" can vary in the number of words to describe the product so a count of words from left to right won't work. For example, it could have in the cell below it, "Doritos cool ranch flavor 10/16oz"...

I need a macro to take the data in the "B" column and look for the last piece of data, whatever is after the "last space" in the description. I then need it to then look for a delimiter which I believe is the "/". If there's a "/", then I want it to take the number directly before the "/" (and after the preceding space) and place it in the next column "C" (which I have created for "count"), then take the items after the "/" and place it in column "D" for product weight and delete the information that it used from the original cell in column "B".

A few items don't have the "/" and just have a count, if this is the case, I just want the information moved to the "C" column.

Again, I thank all of you for any help you can provide.

 

[NeonRedSharpie]

Sub splitInventory()

    maxRow = Cells(Rows.Count, "B").End(xlUp).Row
    
    
    With Range(Cells(2, 3), Cells(maxRow, 3))
        .FormulaR1C1 = "=RIGHT(LEFT(RC[-1],FIND(""/"",RC[-1])-1),LEN(LEFT(RC[-1],FIND(""/"",RC[-1])-1))-FIND(""|"",SUBSTITUTE(LEFT(RC[-1],FIND(""/"",RC[-1])-1),"" "",""|"",LEN(LEFT(RC[-1],FIND(""/"",RC[-1])-1))-LEN(SUBSTITUTE(LEFT(RC[-1],FIND(""/"",RC[-1])-1),"" "","""")))))"
        .Value = .Value
    End With
    
    With Range(Cells(2, 4), Cells(maxRow, 4))
        .FormulaR1C1 = "=SUBSTITUTE(RIGHT(RC[-2],LEN(RC[-2])-FIND(""/"",RC[-2])),"" "","""")"
        .Value = .Value
    End With
    
    Columns(2).Delete


End Sub

The formula in column C is a doozie...but it works on sample data. This will also remove the space between 8 and oz if it exists.

 

[mellojeff]

NeonRedSharpie,

I'm grateful for the help, it's been a CRAZY week here and I finally got a chance to play around with this. I've heard it said that there's always light at the end of the tunnel. I have discovered that it's the headlight of the oncoming train, lol.

I had some issues with "permissions" to overcome (boy, it has been a while) and finally got the editor open to drop it in and give it a whirl.

It didn't come up with the expected results.

Here's an example of what I have for a work book page, I thank you for your help.

[TABLE="width: 744"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Item #[/TD]
[TD]Description[/TD]
[TD]Packaging[/TD]
[TD]size/weight[/TD]
[TD]UPC Number[/TD]
[/TR]
[TR]
[TD]Sales[/TD]
[TD]Class 25 025 MEAT SNACKS[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]025785[/TD]
[TD]BRIDGFORD BEEF & CHEESE 8/1.25 OZ[/TD]
[TD]8[/TD]
[TD]1.25 oz[/TD]
[TD]047500014519[/TD]
[/TR]
[TR]
[TD]025784[/TD]
[TD]BRIDGFORD BEEF SNACK BITES 6/4 OZ[/TD]
[TD]6[/TD]
[TD]4 oz[/TD]
[TD]047500014403[/TD]
[/TR]
[TR]
[TD]025786[/TD]
[TD]BRIDGFORD PEPPERONI SNACK BITE 6/4Z[/TD]
[TD]6[/TD]
[TD]4 oz[/TD]
[TD]047500013581[/TD]
[/TR]
[TR]
[TD]025693[/TD]
[TD]BULL'S 3/$1.00 BIG SNACK STK 100 CT[/TD]
[TD]100[/TD]
[TD] [/TD]
[TD]727968700475[/TD]
[/TR]
[TR]
[TD]025701[/TD]
[TD]BULLS BIGGEST SNK STK **** PP.99 24[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]727968500303[/TD]
[/TR]
[TR]
[TD]025700[/TD]
[TD]BUTTERBALL TURKEY APPLE NUGG 8/3 OZ[/TD]
[TD]8[/TD]
[TD]3 oz[/TD]
[TD]10020501111118[/TD]
[/TR]
[TR]
[TD]025691[/TD]
[TD]DUCK DYNASTY HCKRY BACON JRKY 8/3OZ[/TD]
[TD]8[/TD]
[TD]3 oz[/TD]
[TD]20020501273844[/TD]
[/TR]
[TR]
[TD]025690[/TD]
[TD]DUCK DYNASTY ORIG BEEF JERKY 8/3 OZ[/TD]
[TD]8[/TD]
[TD]3 oz[/TD]
[TD]20020501273806[/TD]
[/TR]
[TR]
[TD]025692[/TD]
[TD]DUCK DYNASTY TERI BEEF JERKY 8/3 OZ[/TD]
[TD]8[/TD]
[TD]3 oz[/TD]
[TD]20020501273813[/TD]
[/TR]
[TR]
[TD]025697[/TD]
[TD]HANNAH'S BIG PLKD SAUSAGE 16 CT JAR[/TD]
[TD]16[/TD]
[TD] [/TD]
[TD]727968110427[/TD]
[/TR]
[TR]
[TD]025694[/TD]
[TD]HANNAH'S TNT HOT SAUSAGE 24/.99[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]727968510920[/TD]
[/TR]
[TR]
[TD]025695[/TD]
[TD]HANNAH'S 2/1.00 REDHOT SAUSAGE 50CT[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]727968110519[/TD]
[/TR]
[TR]
[TD]025696[/TD]
[TD]HANNAN MEXICANA REDHOT SAUSAGE 6/20[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]727968510418[/TD]
[/TR]
[TR]
[TD]025016[/TD]
[TD]JACK LINK BBQ BEEFSTEAK 12[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]017082003836[/TD]
[/TR]
[TR]
[TD]025805[/TD]
[TD]JACK LINK BF JERK SWT/HOT 8/3.25 BX[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]00017082474582[/TD]
[/TR]
</tbody>[/TABLE]

 

[NeonRedSharpie]

So, what did my macro return?

 

[mellojeff]

NeonRedSharpie,

Sorry to take so long to get back to you, I've been dealing with some medical complications for myself and my mother whom I care for.

I'm afraid that the site doesn't allow me to upload pics of the issue so I'll do my best to describe it.

In running the macro, column B & C are deleted. They are replaced with the following information;

Column B now has the following repeated all the way down:=RIGHT(LEFT(RC[-1],FIND("/",RC[-1])-1),LEN(LEFT(RC[-1],FIND("/",RC[-1])-1))-FIND("|",SUBSTITUTE(LEFT(RC[-1],FIND("/",RC[-1])-1)," ","|",LEN(LEFT(RC[-1],FIND("/",RC[-1])-1))-LEN(SUBSTITUTE(LEFT(RC[-1],FIND("/",RC[-1])-1)," ","")))))

Column C now has this all the way down:=SUBSTITUTE(RIGHT(RC[-2],LEN(RC[-2])-FIND("/",RC[-2]))," ","")

If you want to email me, I can forward you a copy of what I'm getting, pics and the workbook for you to check out. my email is the same as my username but at gmail.

Any help is greatly appreciated....

God bless,

Jeffrey

 

[mellojeff]

Can anyone help me with this problem?