Stripping leading and trailing commas

[Skebo]

C2 contains text that may or may not begin, end, or end&begin with a comma. The text may also have commas in the middle. I want to only strip the commas if they appear on either end.
,foo,bar, -> foo,bar
,foo,bar -> foo,bar
foo,bar, -> foo,bar
,, ->

I've been able to accomplish this task with multiple cells, but I figure somebody else could greatly improve upon my feeble attempt.

In B2 I use =IF(RIGHT(C2,1)=",",REPLACE(C2,LEN(C2),1,""),C2) to strip off trailing commas and
In A2 I use =IF(LEFT(B2,1)=",",REPLACE(B2,1,1,""),B2) to strip off leading commas.

In general I'm having trouble figuring out how to have multiple IFs work against a text string.

TIA.

 

[Dave Hawley]

Hi

Try this way:

=MID(C2,IF(FIND(",",C2)=1,2,1),IF(RIGHT(C2)=",",LEN(C2)-2,LEN(C2)))

 

[Aladin Akyurek]

On 2002-03-13 00:51, Skebo wrote:
C2 contains text that may or may not begin, end, or end&begin with a comma. The text may also have commas in the middle. I want to only strip the commas if they appear on either end.
,foo,bar, -> foo,bar
,foo,bar -> foo,bar
foo,bar, -> foo,bar
,, ->

I've been able to accomplish this task with multiple cells, but I figure somebody else could greatly improve upon my feeble attempt.

In B2 I use =IF(RIGHT(C2,1)=",",REPLACE(C2,LEN(C2),1,""),C2) to strip off trailing commas and
In A2 I use =IF(LEFT(B2,1)=",",REPLACE(B2,1,1,""),B2) to strip off leading commas.

In general I'm having trouble figuring out how to have multiple IFs work against a text string.

TIA.

Skebo,

How about:

=IF(AND(LEFT(A1)=",",RIGHT(A1)=","),MID(A1,2,LEN(A1)-2),IF(LEFT(A1)=",",MID(A1,2,LEN(A1)),IF(RIGHT(A1)=",",LEFT(A1,LEN(A1)-1),A1)))

where A1 houses the target string.

Aladin

 

[smozgur]

=IF(LEFT(C2,1)=",",IF(RIGHT(RIGHT(C2,LEN(C2)-1),1)=",",LEFT(RIGHT(C2,LEN(C2)-1),LEN(RIGHT(C2,LEN(C2)-1))-1),C2),IF(RIGHT(C2,1)=",",LEFT(C2,LEN(C2)-1),C2))

regards
suat

 

[Mark W.]

=SUBSTITUTE(TRIM(SUBSTITUTE(A1,","," "))," ",",")

 

[Aladin Akyurek]

Skebo,

Use the shorter formula Mark suggested.

I believe those IFs are not entirely wasted as you were interested in its use explicitly. A small compensation I'd say

Aladin

On 2002-03-13 01:14, Aladin Akyurek wrote:

On 2002-03-13 00:51, Skebo wrote:
C2 contains text that may or may not begin, end, or end&begin with a comma. The text may also have commas in the middle. I want to only strip the commas if they appear on either end.
,foo,bar, -> foo,bar
,foo,bar -> foo,bar
foo,bar, -> foo,bar
,, ->

I've been able to accomplish this task with multiple cells, but I figure somebody else could greatly improve upon my feeble attempt.

In B2 I use =IF(RIGHT(C2,1)=",",REPLACE(C2,LEN(C2),1,""),C2) to strip off trailing commas and
In A2 I use =IF(LEFT(B2,1)=",",REPLACE(B2,1,1,""),B2) to strip off leading commas.

In general I'm having trouble figuring out how to have multiple IFs work against a text string.

TIA.

Skebo,

How about:

=IF(AND(LEFT(A1)=",",RIGHT(A1)=","),MID(A1,2,LEN(A1)-2),IF(LEFT(A1)=",",MID(A1,2,LEN(A1)),IF(RIGHT(A1)=",",LEFT(A1,LEN(A1)-1),A1)))

where A1 houses the target string.

Aladin

 

[Skebo]

Sorry, I forgot to mention a case that obviously makes a big difference <please don't be too hard on me> Sometimes there will be cases were the data has ,, or ,,, or ,,,, etc. These may appear in the middle or on the ends of the string. If they are in the middle multiple commas should change to one comma. No commas should ever appear on the endpoints.

foo,,bar -> foo,bar
foo,,bar, -> foo,bar
foo,bar,, -> foo,bar
,,,foo,,bar,, -> foo,bar

My deepest apologies for leaving this important detail out.

 

[Mark W.]

On 2002-03-13 11:07, Skebo wrote:
Sorry, I forgot to mention a case that obviously makes a big difference <please don't be too hard on me> Sometimes there will be cases were the data has ,, or ,,, or ,,,, etc. These may appear in the middle or on the ends of the string. If they are in the middle multiple commas should change to one comma. No commas should ever appear on the endpoints.

foo,,bar -> foo,bar
foo,,bar, -> foo,bar
foo,bar,, -> foo,bar
,,,foo,,bar,, -> foo,bar

My deepest apologies for leaving this important detail out.

=SUBSTITUTE(TRIM(SUBSTITUTE(A1,","," "))," ",",")

...will work for these cases as well.

 

[Skebo]

Mark,

I must say that is very slick.

Thanks a bunch!

 

[sgymer]

=SUBSTITUTE(TRIM(SUBSTITUTE(A1,","," "))," ",",")

...will work for these cases as well.

I have a minor problem with this:

If there's a space in one of the words then it replaces that space with a comma. For example:

ANAHEIM, NY RANGERS, NY ISLANDERS, OTTAWA, , , , , , , , ,
-> ANAHEIM,NY,RANGERS,NY,ISLANDERS,OTTAWA

Desired output:
ANAHEIM,NY RANGERS, NY ISLANDERS,OTTAWA

So basically all I need is to strip the commas from the end. I'm sorry if this is a stupid question, but I just happened to find this thread and tried out Mark's solution... Any help?