# Need Help On A Formula

#### ranjith2523

##### Board Regular
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)))))))

Regards,
Ranjith

Formula.xlsx
ABCD
1ProductsProduct LengthExpected ResultExpected Length
2A0CA#0D18A0CA 0D115
3B0NAA#ABC9B0NAA ABC15
4A0C39A#0D110A0C39A 0D115
5HA106A3#78L11HA106A3 78L15
6R4R55ACR#0D112R4R55ACR 0D115
7R4R55ACRG#0D113R4R55ACRG 0D115
8HA106A356C#78L14HA106A356C 78L15
9ABCDAR6ABCDAR6
10HA106A37HA106A37
Sheet1
Cell Formulas
RangeFormula
B2:B10,D2:D10B2=LEN(A2)
C2:C7C2=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:C10C8=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)))))))

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### ranjith2523

##### Board Regular
+Fluff 1.xlsm
ABCD
1ProductsProduct LengthExpected Length
2A0CA#0D18A0CA 0D115
3B0NAA#ABC9B0NAA ABC15
4A0C39A#0D110A0C39A 0D115
5HA106A3#78L11HA106A3 78L15
6R4R55ACR#0D112R4R55ACR 0D115
7R4R55ACRG#0D113R4R55ACRG 0D115
8HA106A356C#78L14HA106A356C 78L15
9ABCDAR6ABCDAR6
10HA106A37HA106A37
Master
Cell Formulas
RangeFormula
C2:C10C2=SUBSTITUTE(A2,"#",REPT(" ",16-LEN(A2)))
D2:D10D2=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

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
5
Views
96
Replies
3
Views
87
Replies
4
Views
562
Replies
6
Views
116
Replies
6
Views
292

1,136,275
Messages
5,674,782
Members
419,524
Latest member
helensesc

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back