How to add multiple slashes to a single cell

0
votes

I have a column with about 5k rows that I need to add slashes to. I need to add slashes to the first, second, and last space (" ") in the cell. Each cell has different lengths so there are different amounts of spaces between the second and last in each row. Here's an example of some rows

NYLA D DURA FEMUR BCN LG
NULO D FZD GF BF 5oz
OMNI D BRTSH SLP LD GRN 4ft
OMNI D LIGHT S-HOOK
OMNI D SS BOWL 3qt

I need these converted to

NYLA/D/DURA FEMUR BCN/LG
NULO/D/FZD GF BF/5oz
OMNI/D/BRTSH SLP LD GRN/4ft
OMNI/D/LIGHT S-HOOK
OMNI/D/SS BOWL/3qt

I have tried using the substitute formula but that will only add one slash when I need to add 1-3. I'm not sure if nesting the substitute formula is possible in this scenario. If so, that should do it but I can't get it to work.

2
excel-formula
why is OMNI D LIGHT S-HOOK not OMNI/D/LIGHT/S-HOOK? – Scott Craner
Hey Scott, sorry, that wasn't the best example to add since it was entered incorrectly to begin with. The last section after the last slash in the cell is the size or weight of the product so it should be OMNI/D/S-HOOK/LIGHT. – CTEAGUE212

2 Answers

1
votes

Nested SUBSTITUTE is the way to go:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ","/",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))," ","/",2)," ","/",1)

enter image description here

1
votes

You can achieve this with the following formula:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ","/",LEN(SUBSTITUTE(A1," ","  "))-LEN(A1))," ","/",1)," ","/",1)

It replaces the last instance of " " followed by the first two instances.

This might however return an error if there are less than three " " in the string.