ranjith2523
Board Regular
- Joined
- Apr 29, 2014
- Messages
- 137
- Office Version
- 365
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
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))))))) |