Urgent Help Need On A Formula

[ranjith2523]

Hi Friends,

Hope everyone doing good !!!

After a long time, again i am seeking experts help.

I have a data like i provided in first column in below Table.

I have applied the formula "=SUBSTITUTE(A2," ","#",1)" in cell B2 and i got the expected output in column B as expected in 3rd column for first product alone.

The cell A2 has 7 spaces and my above substitute formula has 7 spaces hence i got the result which i expected.

But my expectation is, i need a single formula which should remove all the spaces and replace with "#" as i mentioned in column C.

I hope some excel experts will help me to get the formula which i am looking for.

Thanks for all your help in advance.

Regards,
Ranjith

Data	Output	Expected
HA325A1 7WR	HA325A1#7WR	HA325A1#7WR
HA325A1 7WR	HA325A1 7WR	HA325A1#7WR
HA325A1 7WR	HA325A1 7WR	HA325A1#7WR
HA325A1 7WS	HA325A1 7WS	HA325A1#7WS
HA325A1 7X2	HA325A1 7X2	HA325A1#7X2
HA325A1 7XC	HA325A1 7XC	HA325A1#7XC
HA325AB1 7XC	HA325AB1 7XC	HA325AB1#7XC

 

[Joe4]

Yes, i tried that in wrong order How stupid i am

Thanks for your time and help Joe .. much appreciated..

You are welcome.

Don't be so hard on yourself! Its good that you were able to figure out which functions were needed.
Sometimes, it can be a little tricky to get it just right, and a little help is required.

 

[ranjith2523]

You are welcome.

Don't be so hard on yourself! Its good that you were able to figure out which functions were needed.
Sometimes, it can be a little tricky to get it just right, and a little help is required.

Thanks for your kind advice !!! It means a lot In today's world peoples don't even like to help others but you peoples are exception.. You are not only just helping you are feeding knowledge and that makes some difference in skill set which helps on their career. I am not saying this for just helping on my formula .. You peoples are helping so many peoples .. Hats off to you all

 

[Joe4]

Thanks for your kind advice !!! It means a lot In today's world peoples don't even like to help others but you peoples are exception.. You are not only just helping you are feeding knowledge and that makes some difference in skill set which helps on their career. I am not saying this for just helping on my formula .. You peoples are helping so many peoples .. Hats off to you all

Thank you for the kind words!