Reduce string length by -1 delimiter at the time

[psc1]

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

 

[Peter_SSs]

This could be done without the helper cells that I have used but this way keeps the formulas a bit simpler. Is that any use to you?

21 03 25.xlsm
	A	B	C
1
2
3		a/b/c/d/e/f	5
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	4
11		1/2/3/4
12		1/2/3
13		1/2
14		1
Reduce by delimiter

Cell Formulas
Range	Formula
C3,C10	C3	=LEN(B3)-LEN(SUBSTITUTE(B3,"/",""))
B4:B8	B4	=LEFT(B$3,FIND("#",SUBSTITUTE(B3,"/","#",C$3-ROWS(B$4:B4)+1))-1)
B11:B14	B11	=LEFT(B$10,FIND("#",SUBSTITUTE(B10,"/","#",C$10-ROWS(B$11:B11)+1))-1)

 

[Rick Rothstein]

Here is one way to do it...

=LEFT(B3,FIND("\",SUBSTITUTE(B3,"/","\",LEN(B3)-LEN(SUBSTITUTE(B3,"/",""))))-1)

 

[psc1]

Hi Peter,
This totally makes sense thanks
How would you apply this to the other strings in B column?
Must I copy/paste this formula for each string or can it be rolled out to the rest of B column?
Many thanks

Book2
	A	B	C	D
1
2
3		a/b/c/d/e/f	5
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	4
11		1/2/3/4
12		1/2/3
13		1/2
14		1
15
16		aa/bb/cc/dd
17		a1/b1/c1/d1
18		1a1/1b1/1c1/1d1
19
20
Sheet1

Cell Formulas
Range	Formula
C3,C10	C3	=LEN(B3)-LEN(SUBSTITUTE(B3,"/",""))
B4:B8	B4	=LEFT(B$3,FIND("#",SUBSTITUTE(B3,"/","#",C$3-ROWS(B$4:B4)+1))-1)
B11:B14	B11	=LEFT(B$10,FIND("#",SUBSTITUTE(B10,"/","#",C$10-ROWS(B$11:B11)+1))-1)

 

[Peter_SSs]

How would you apply this to the other strings in B column?
Must I copy/paste this formula for each string or can it be rolled out to the rest of B column?

Suggest that you try Rick's formula. It might be easier to apply to different sections in the column.

 

[psc1]

Here is one way to do it...

=LEFT(B3,FIND("\",SUBSTITUTE(B3,"/","\",LEN(B3)-LEN(SUBSTITUTE(B3,"/",""))))-1)

Hello Rick
Thank you for your help
Tested your formula and it works for the last value
How do I carry on the theme of removing last delimiter all the way to the last value (trying to avoid copy/paste for each results)?
Cheers

 

[psc1]

Suggest that you try Rick's formula. It might be easier to apply to different sections in the column.

Thanks for your help and fast reply Peter.
Really appreciate

 

[Rick Rothstein]

I'm about to go to sleep for the night so I won't be able to follow up until tomorrow. However, I am not sure what your actual question is. Using an example of, say, three values... what does your data look like BEFORE any attempt is made to split it apart? What would that data look like after it has been split apart. What I am unclear on is how your existing data is laid out and exactly where the split apart values are to be placed.

Peter... if you are still following this thread and know what the OP is asking for, please feel free to jump in so the OP doesn't have to wait until I can get back here.

 

[Peter_SSs]

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)

 

[psc1]

I'm about to go to sleep for the night so I won't be able to follow up until tomorrow. However, I am not sure what your actual question is. Using an example of, say, three values... what does your data look like BEFORE any attempt is made to split it apart? What would that data look like after it has been split apart. What I am unclear on is how your existing data is laid out and exactly where the split apart values are to be placed.

Peter... if you are still following this thread and know what the OP is asking for, please feel free to jump in so the OP doesn't have to wait until I can get back here.

Thanks Rick for your time.
I will formulate in better terms what it is for and what is required then send an example
Have a good night