Stripping leading and trailing commas

Skebo

New Member
Joined
Feb 27, 2002
Messages
22
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.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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
 
Upvote 0
=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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
=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?
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

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
Back
Top