Need Help On A Formula

ranjith2523

Board Regular
Joined
Apr 29, 2014
Messages
78
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
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

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,467
Office Version
  1. 365
Platform
  1. Windows

ranjith2523

Board Regular
Joined
Apr 29, 2014
Messages
78
How about
+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
Joined
Jun 12, 2014
Messages
56,467
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,361
Messages
5,635,808
Members
416,884
Latest member
leeshjay

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top