Reduce string length by -1 delimiter at the time

psc1 · Mar 25, 2021

Hello
Could one of you genius work that one out for me please

Trying to break down all strings length in a column by reducing the last value with its delimiter at each step .. all the way down to the last value
I tried to no avail and my head hurts

Book1

A

B

C

1

2

Original strings

3

a/b/c/d/e/f

4

With formula

5

a/b/c/d/e

6

a/b/c/d

7

a/b/c

8

a/b

9

a

10

1/2/3/4/5

11

With formula

12

1/2/3/4

13

1/2/3

14

1/2

15

1

16

17

Sheet1

psc1 · Mar 25, 2021

Peter_SSs said:
If your original strings are scattered down column B and you want to avoid multiple copy/past, perhaps you could use another column for the results, like this where you just enter the first formula and copy down as far as you might need.

21 03 25.xlsm
A B C
1
2
3 a/b/c/d/e/f a/b/c/d/e/f
4 a/b/c/d/e
5 a/b/c/d
6 a/b/c
7 a/b
8 a
9
10 1/2/3/4/5 1/2/3/4/5
11 1/2/3/4
12 1/2/3
13 1/2
14 1
15
16
17 aa/bb/cc/dd aa/bb/cc/dd
18 aa/bb/cc
19 aa/bb
20 aa
21
Reduce by delimiter (2)
Cell Formulas
Range Formula
C3:C21 C3 =IF(B3="",IFERROR(LEFT(C2,FIND("\",SUBSTITUTE(C2,"/","\",LEN(C2)-LEN(SUBSTITUTE(C2,"/",""))))-1),""),B3)

Thanks Peter,
This is the right logic to apply

I am trying to create missing strings from a list.
I need for all values a, b, c .. their correct path (a=main, a/b=sub, a/b/c=sub ...)
This is the only way the system will accept any upload with the correct path..
Following your logic, in the below example I managed to extract missing (sometimes not) strings in different columns.
I was aiming to regroup all results in a single column and then delete Duplicates...
This should get me the complete list of all links from Main>Sub>...>Sub in their logical order.
Cheers

Book4

A

B

C

D

E

F

G

H

I

1

Name

Parent

Path

Path -1

2

3

Clothes/Men

4

Clothes/Men/Tshirt

5

Clothes/Men/Tshirt/XL

6

This is the correct logical path to upload categories

7

8

Clothes/Women/Tshirt

9

Some categories pop up without their Parents - so they must be created below

10

clothes/women

11

12

13

I must find a way to regroup E,F,G results under D column so the correct path to Main> Sub…>Sub category is restored

14

15

16

And then Duplicates will be deleted from this column

17

Sheet1

Cell Formulas
Range	Formula
A10,A8,A2:A5	A2	=IFERROR(RIGHT(D2,LEN(D2)-FIND("$",SUBSTITUTE(D2,"/","$",LEN(D2)-LEN(SUBSTITUTE(D2,"/",""))))),#REF!)
B10:B11,B8,B2:B5	B2	=TRIM(LEFT(RIGHT(SUBSTITUTE(D2,"/",REPT(" ",100)),200),100))
E10:H11,E8:H8,E2:H5	E2	=LEFT(D2,FIND("\",SUBSTITUTE(D2,"/","\",LEN(D2)-LEN(SUBSTITUTE(D2,"/",""))))-1)

Rick Rothstein · Mar 25, 2021

I am afraid your last post has left me more confused. Your original request asked us to start with a "path" and reduce it one "level" at a time... your Column D appears to be the opposite (one "level" building up to a full "path"). Also, I am not completely sure what is existing data and what is calculated data. I think it will make things clear for us if you would post to sheet views... the first of just your existing data (as you see it before you do any processing to it... do not add any comments, just show us what your existing data looks like, nothing more) and the second of what the sheet should look like after all the processing is complete.

psc1 · Mar 25, 2021

Rick Rothstein said:
I am afraid your last post has left me more confused. Your original request asked us to start with a "path" and reduce it one "level" at a time... your Column D appears to be the opposite (one "level" building up to a full "path"). Also, I am not completely sure what is existing data and what is calculated data. I think it will make things clear for us if you would post to sheet views... the first of just your existing data (as you see it before you do any processing to it... do not add any comments, just show us what your existing data looks like, nothing more) and the second of what the sheet should look like after all the processing is complete.

Hello Rick
Thank you for your feedback and apology for confusing you.
I'll try to explain plainly..
"B" is the existing data "C, D, E" are calculated (-1 value everytime)
In order for me to upload categories in a chronological way (main>sub>...>sub) they must all be present in column "B" under this format:
a
a/b
a/b/c
...
Currently some categories in "B" are listed with the missing links (i.e "B3" missing a - a/b - a/b/c) - Hence formula to extract -1 value all the way to the root.
I intended to recombine results from "C, D, E" into "B" and remove duplicates so I get a complete chronological path to upload.
This is the only way I figured out to get to it..
I hope it makes sense

Book1

A

B

C

D

E

1

Original Path

Path -1

Path -2

Pathe -3

2

Antiques-collectables / Advertising

Cell Formulas
Range	Formula
C3:C21	C3	=IF(B3="",IFERROR(LEFT(C2,FIND("\",SUBSTITUTE(C2,"/","\",LEN(C2)-LEN(SUBSTITUTE(C2,"/",""))))-1),""),B3)

Cell Formulas
Range	Formula
C2:E18	C2	=LEFT(B2,FIND("\",SUBSTITUTE(B2,"/","\",LEN(B2)-LEN(SUBSTITUTE(B2,"/",""))))-1)

Reduce string length by -1 delimiter at the time

psc1

New Member

psc1

New Member

Excel Facts

Rick Rothstein

MrExcel MVP

psc1

New Member

Rick Rothstein

MrExcel MVP

psc1

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock