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]

Your formula seems to work for me, when applied to each value in column A.
In all instances, it seems to return the "Expected" value you are showing. So what is the issue?

If you want to replace them right in column A all at once, simply select column A, and do a Find/Replace, replacing a single space with a "#", making sure to select the "Replace All" option.

 

[Eric W]

Maybe:

=SUBSTITUTE(TRIM(A2)," ","#")

 

[Joe4]

Maybe:

=SUBSTITUTE(TRIM(A2)," ","#")

A ha!

So maybe the issue they are trying to say is that there are multiple consecutive spaces?
If so, it obviously did not show up well on the example (hence my confusion).

 

[ranjith2523]

Your formula seems to work for me, when applied to each value in column A.
In all instances, it seems to return the "Expected" value you are showing. So what is the issue?

If you want to replace them right in column A all at once, simply select column A, and do a Find/Replace, replacing a single space with a "#", making sure to select the "Replace All" option.

Hi Joe,

Thanks for your quick response and really sorry for the confusion and wasting your precious time.

I just noticed, this webpage automatically trimmed the data in column A which i provided. Actually the first product has 7 spaces second one has 6, 3rd one have 5 so on..

HA325A1 7WR
HA325A1 7WR
HA325A1 7WR
HA325A1 7WS
HA325A1 7X2
HA325A1 7XC
HA325A1 7XC

This is what the data which i actually input in column A.

Yes, the formula works fine if the product exists with 7 spaces if not it wont work as expected.

Even i tried "TRIM(SUBSTITUTE(" but it didn't work as expected.

The expectation is the formula should replace all the spaces even if have 5 spaces or 4 spaces or 1 it should replace with "#".

Regards,
Ranjith

 

[Joe4]

Thanks for your quick response and really sorry for the confusion and wasting your precious time.

No worries. Take a look at Eric's solution.
It looks like you might have tried the correct functions, just in the wrong order!

 

[ranjith2523]

Maybe:

=SUBSTITUTE(TRIM(A2)," ","#")

Hi Eric,

Thank you so much It really works as expected. Actually i tried like this and got incorrect result "=TRIM(SUBSTITUTE(A2," ","#"))"

Thanks again for your help here

Actually, this webpage automatically trimmed the data which i provided in column A but still you provided the solution. You are an expert

Thank you so much again

Regards,
Ranjith

 

[ranjith2523]

No worries. Take a look at Eric's solution.
It looks like you might have tried the correct functions, just in the wrong order!

Hi Joe,

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

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

Regards,
Ranjith

 

[Eric W]

Yes, I suspected that multiple spaces was the problem, and the website trimmed them making it hard to see. When displaying a formula, you might try wrapping it in the fx XLS wrapper, which preserves the spaces, like this:

=SUBSTITUTE(A2,"       ","#",1)

But it looks like you got it working! Glad we could help.

 

[ranjith2523]

Yes, I suspected that multiple spaces was the problem, and the website trimmed them making it hard to see. When displaying a formula, you might try wrapping it in the fx XLS wrapper, which preserves the spaces, like this:

=SUBSTITUTE(A2,"       ","#",1)

But it looks like you got it working! Glad we could help.

Thanks for your guidance. Surely, will do in future posts.