Replace Comma's with Dash
 

Hello,

Please could someone help me. In cell A1 of my spreadsheet I have
"ABC,DEF,GHI". I would like B1 to contain the same data as A1 but
with a dash instead of a comma i.e. ABC-DEF-GHI. The number of
comma's will never be the same so all comma's need to be replaced. I
need to use a formula otherwise I would have used find and replace.

Can anyone help.

=REPLACE(A1,FIND(",",A1,1),1,"-")

I used this but it only replaces the first comma, I need to find out
how many comma's there are in A1.

Thanks,

Brett

Answer: Replace Comma's with Dash
 

Hi Brett,

You can use the SUBSTITUTE function in Excel to replace all commas with dashes in cell A1. Here's how:

1. In cell B1, enter the following formula:
   Formula:

   ---

   =SUBSTITUTE(A1,",","-") 


   ---

2. Press Enter.

This formula will replace all commas in cell A1 with dashes and display the result in cell B1.

If you want to count the number of commas in cell A1, you can use the LEN and SUBSTITUTE functions together. Here's how:

1. In cell B1, enter the following formula:
   Formula:

   ---

   =LEN(A1)-LEN(SUBSTITUTE(A1,",","")) 


   ---

2. Press Enter.

This formula will count the number of commas in cell A1 and display the result in cell B1.

Replace Comma's with Dash
 

you can use replace command (Ctrl-H).
best wishes
sreedhar


" wrote:

Hello,

Please could someone help me. In cell A1 of my spreadsheet I have
"ABC,DEF,GHI". I would like B1 to contain the same data as A1 but
with a dash instead of a comma i.e. ABC-DEF-GHI. The number of
comma's will never be the same so all comma's need to be replaced. I
need to use a formula otherwise I would have used find and replace.

Can anyone help.

=REPLACE(A1,FIND(",",A1,1),1,"-")

I used this but it only replaces the first comma, I need to find out
how many comma's there are in A1.

Thanks,

Brett

Replace Comma's with Dash
 

Try this instead...

=SUBSTITUTE(A1,",","-")

Rick


wrote in message
...
Hello,

Please could someone help me. In cell A1 of my spreadsheet I have
"ABC,DEF,GHI". I would like B1 to contain the same data as A1 but
with a dash instead of a comma i.e. ABC-DEF-GHI. The number of
comma's will never be the same so all comma's need to be replaced. I
need to use a formula otherwise I would have used find and replace.

Can anyone help.

=REPLACE(A1,FIND(",",A1,1),1,"-")

I used this but it only replaces the first comma, I need to find out
how many comma's there are in A1.

Thanks,

Brett