if and replace function?

  • Thread starter Thread starter jc9972003
  • Start date Start date
J

jc9972003

Hello
I need help
I have names on my left and depending on the name I need to remove a space

Original data Answer
John, Repp John,Repp
Jay, Thorp Jay, Thorp
Jana, Washington Jana,Washington

So just depending on the name some need the space after the "," trim and some dont I have a total of about 40 names only about 6 dont need the space after the coma.
I was thinking of Replace and If But I cant seem to put it together.
 
Hi,

Am Wed, 22 May 2013 10:57:40 -0700 (PDT) schrieb (e-mail address removed):
I have names on my left and depending on the name I need to remove a space

Original data Answer
John, Repp John,Repp
Jay, Thorp Jay, Thorp
Jana, Washington Jana,Washington

So just depending on the name some need the space after the "," trim and some dont I have a total of about 40 names only about 6 dont need the space after the coma.
I was thinking of Replace and If But I cant seem to put it together.
Click to expand...

I don't understand your example. In which case the space will be
removed? Why does the space in the second line remain?


Regards
Claus Busch
 
Hello I need help I have names on my left and depending on the name I need to remove a space Original data Answer John, Repp John,Repp Jay, Thorp Jay, Thorp Jana, Washington Jana,Washington So just depending on the name some need the space after the "," trim and some dont I have a total of about 40 names only about 6 dont need the space after the coma. I was thinking of Replace and If But I cant seem to put it together.
Click to expand...



There are certain names that have to remain the same as the original data
but another 36 or so I have to trim the space after the comma
So I would have to hard code those 36 names in the formula if you can just hard code 2 for me I can do the rest.
 
Hi,

Am Wed, 22 May 2013 11:51:57 -0700 (PDT) schrieb (e-mail address removed):
There are certain names that have to remain the same as the original data
but another 36 or so I have to trim the space after the comma
So I would have to hard code those 36 names in the formula if you can just hard code 2 for me I can do the rest.
Click to expand...

your names in column A. I wrote the names that will remain the space in
column H. Then in B1:
=IF(COUNTIF($H$1:$H$36,A1)=0,SUBSTITUTE(A1,", ",","),A1)


Regards
Claus Busch
 
Hi again,

Am Wed, 22 May 2013 20:58:39 +0200 schrieb Claus Busch:
=IF(COUNTIF($H$1:$H$36,A1)=0,SUBSTITUTE(A1,", ",","),A1)
Click to expand...

if there are only 6 names to remove the space it is easier to write this
6 names in column H and then:
=IF(COUNTIF($H$1:$H$6,A1)>0,SUBSTITUTE(A1,", ",","),A1)



Regards
Claus Busch
 
Hello I need help I have names on my left and depending on the name I need to remove a space Original data Answer John, Repp John,Repp Jay, Thorp Jay, Thorp Jana, Washington Jana,Washington So just depending on the name some need the space after the "," trim and some dont I have a total of about 40 names only about 6 dont need the space after the coma. I was thinking of Replace and If But I cant seem to put it together.
Click to expand...



Thank you Clause this works great
 
Back
Top