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.

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

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[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?