Need Help On A Formula

[ranjith2523]

Hi Friends,

I need to simplify the formula which i currently use and let me clearly explain you the needs.

The process is to check whether # exists in Column A - if it exists then some space should be added until it reach the length 15 and return the value in Column C - if # not found in Column A then no changes needed we can return the same value in column C.

I wrote the below formula and it works as expected but i feel this is a kind of lengthy formula, is there any way to simplify the formula to bring the same result ?

=IF(LEN(A2)=8,SUBSTITUTE(TRIM(A2),"#"," "),IF(LEN(A2)=9,SUBSTITUTE(TRIM(A2),"#"," "),IF(LEN(A2)=10,SUBSTITUTE(TRIM(A2),"#"," "),IF(LEN(A2)=11,SUBSTITUTE(TRIM(A2),"#"," "),IF(LEN(A2)=12,SUBSTITUTE(TRIM(A2),"#"," "),IF(LEN(A2)=13,SUBSTITUTE(TRIM(A2),"#"," "),IF(LEN(A2)=14,SUBSTITUTE(TRIM(A2),"#"," "),A2)))))))

Thanks for all your help in advance.

Regards,
Ranjith

Formula.xlsx
	A	B	C	D
1	Products	Product Length	Expected Result	Expected Length
2	A0CA#0D1	8	A0CA 0D1	15
3	B0NAA#ABC	9	B0NAA ABC	15
4	A0C39A#0D1	10	A0C39A 0D1	15
5	HA106A3#78L	11	HA106A3 78L	15
6	R4R55ACR#0D1	12	R4R55ACR 0D1	15
7	R4R55ACRG#0D1	13	R4R55ACRG 0D1	15
8	HA106A356C#78L	14	HA106A356C 78L	15
9	ABCDAR	6	ABCDAR	6
10	HA106A3	7	HA106A3	7
Sheet1

Cell Formulas
Range	Formula
B2:B10,D2:D10	B2	=LEN(A2)
C2:C7	C2	=IF(LEN(A2)=8,SUBSTITUTE(TRIM(A2),"#"," "),IF(LEN(A2)=9,SUBSTITUTE(TRIM(A2),"#"," "),IF(LEN(A2)=10,SUBSTITUTE(TRIM(A2),"#"," "),IF(LEN(A2)=11,SUBSTITUTE(TRIM(A2),"#"," "),IF(LEN(A2)=12,SUBSTITUTE(TRIM(A2),"#"," "),IF(LEN(A2)=13,SUBSTITUTE(TRIM(A2),"#"," "),IF(LEN(A2)=14,SUBSTITUTE(TRIM(A2),"#"," "),A2)))))))
C8:C10	C8	=IF(LEN(A8)=8,SUBSTITUTE(TRIM(A8),"#"," "),IF(LEN(A8)=9,SUBSTITUTE(TRIM(A8),"#"," "),IF(LEN(A8)=10,SUBSTITUTE(TRIM(A8),"#"," "),IF(LEN(A8)=11,SUBSTITUTE(TRIM(A8),"#"," "),IF(LEN(A8)=12,SUBSTITUTE(TRIM(A8),"#"," "),IF(LEN(A8)=13,SUBSTITUTE(TRIM(A8),"#"," "),IF(LEN(A8)=14,SUBSTITUTE(TRIM(A8),"#"," "),A8)))))))

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D
1	Products	Product Length		Expected Length
2	A0CA#0D1	8	A0CA 0D1	15
3	B0NAA#ABC	9	B0NAA ABC	15
4	A0C39A#0D1	10	A0C39A 0D1	15
5	HA106A3#78L	11	HA106A3 78L	15
6	R4R55ACR#0D1	12	R4R55ACR 0D1	15
7	R4R55ACRG#0D1	13	R4R55ACRG 0D1	15
8	HA106A356C#78L	14	HA106A356C 78L	15
9	ABCDAR	6	ABCDAR	6
10	HA106A3	7	HA106A3	7
Master

Cell Formulas
Range	Formula
C2:C10	C2	=SUBSTITUTE(A2,"#",REPT(" ",16-LEN(A2)))
D2:D10	D2	=LEN(C2)

 

[ranjith2523]

How about

+Fluff 1.xlsm
	A	B	C	D
1	Products	Product Length		Expected Length
2	A0CA#0D1	8	A0CA 0D1	15
3	B0NAA#ABC	9	B0NAA ABC	15
4	A0C39A#0D1	10	A0C39A 0D1	15
5	HA106A3#78L	11	HA106A3 78L	15
6	R4R55ACR#0D1	12	R4R55ACR 0D1	15
7	R4R55ACRG#0D1	13	R4R55ACRG 0D1	15
8	HA106A356C#78L	14	HA106A356C 78L	15
9	ABCDAR	6	ABCDAR	6
10	HA106A3	7	HA106A3	7
Master

Cell Formulas
Range	Formula
C2:C10	C2	=SUBSTITUTE(A2,"#",REPT(" ",16-LEN(A2)))
D2:D10	D2	=LEN(C2)

Perfect Happy to see the reply from you Fluff after a long time

if possible please give me a short explanation about the REPT formula and how it works in my case.

 

[Fluff]

You're welcome & thanks for the feedback.