How to find and spill the positions of all occurrences of a specific character in a string?

[Rnkhch]

Hello,

This question is related to the FIND or SEARCH functions, but both of these functions return the position of only the first occurrence of the specified character(s) in a string. But how can I get a spill of all positions if the specific character occurs more than once?

For example, in the attached XL2BB blow, how can I get the positions of all the commas in spill outputs? (I mean a separate spill for each of the examples)

Blank power workbook1
	A	B	C	D
1	12,34,A,63,98,D	3	3
2	C,43,B+,17,99	2	2
3	45 , 33, A- ,17,88 , 12 ,39	9	9
4
Sheet2

Cell Formulas
Range	Formula
B1:B3	B1	=FIND(",",A1)
C1:C3	C1	=SEARCH(",",A1)

Thanks for any input!

 

[gelu]

Hi R,

To tell Excel's Find to return the 2nd occurrence the Start_num = 1st +1. the third - 2nd+1, etc
Then it knows where texactly to look for within the string.

G

Book1
	A	B	C	D	E	F	G	H	I	J
1				1st	2nd	3rd	4th	5th	6th	7th
2				Find
3	12,34,A,63,98,D	3		3	6	8	11	14
4	C,43,B+,17,99	2		2	5	8	11
5	45 , 33, A- ,17,88 , 12 ,39	9		9	18	30	33	40	51
Sheet1

Cell Formulas
Range	Formula
D3:D5	D3	=IFERROR(FIND(",",$A3,1),"")
E3:J5	E3	=IFERROR(FIND(",",$A3,D3+1),"")
B3:B5	B3	=FIND(",",A3)

 

[Rnkhch]

Thank you, yes, I was aware of this. But I was wondering if there is a way to automatically get the entire positions in one spill, so that I can store the spill in a LET variable and then supply the spill as input into other functions in the LET.

 

[JGordon11]

milestone.xlsx
	A	B	C	D	E	F	G	H
1	12,34,A,63,98,D	3	6	8	11	14
2	C,43,B+,17,99	2	5	8	11
3	45 , 33, A- ,17,88 , 12 ,39	9	18	30	33	40	51
4
Sheet3

Cell Formulas
Range	Formula
B1:F1,B3:G3,B2:E2	B1	=LET(s, SEQUENCE(1,LEN(A1)), n, (MID(A1,s,1)=",")*s, FILTER(n,n<>0))
Dynamic array formulas.

 

[Rnkhch]

Sweet Exactly what I needed! Thank you!!