| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Replace Comma with Dash
Hello,
Please can someone explain how I can replace commas with a dash/ hyphen. For example cell A1 contains "ABC,DEF,GHI". I would like cell B1 to display "ABC-DEF-GHI". I attempted to use REPLACE(A1,FIND(CHAR",",A1),1,",") but this only replaces the first comma. I need to cound how many comma's are in the text and then replace them. I know I could you a find and replace but I need a formula. The number of commas in the text will vary. Sometimes there are 2 other times there are none. Thanks, Brett |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Replace Comma with Dash
Try,
Edit|Replace Replace what , Replace with - You now have a choice depending on the construction of your worksheet. You can do a replace all but this will do precisely that even for commas you may not want it to or you can step through with Find. Mike " wrote: Hello, Please can someone explain how I can replace commas with a dash/ hyphen. For example cell A1 contains "ABC,DEF,GHI". I would like cell B1 to display "ABC-DEF-GHI". I attempted to use REPLACE(A1,FIND(CHAR",",A1),1,",") but this only replaces the first comma. I need to cound how many comma's are in the text and then replace them. I know I could you a find and replace but I need a formula. The number of commas in the text will vary. Sometimes there are 2 other times there are none. Thanks, Brett |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Replace Comma with Dash
Check out Rick Rothstein's reply in your other post.
His answer works as I have tried it myself. Mark. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Replace Comma with Dash
Was something wrong with the answer I gave you (in your first posting) some
48 minutes before you posted this same question over again? Rick wrote in message ... Hello, Please can someone explain how I can replace commas with a dash/ hyphen. For example cell A1 contains "ABC,DEF,GHI". I would like cell B1 to display "ABC-DEF-GHI". I attempted to use REPLACE(A1,FIND(CHAR",",A1),1,",") but this only replaces the first comma. I need to cound how many comma's are in the text and then replace them. I know I could you a find and replace but I need a formula. The number of commas in the text will vary. Sometimes there are 2 other times there are none. Thanks, Brett |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Replace Comma's with Dash | Excel Worksheet Functions | |||
| replace dash in a number sequence | Excel Worksheet Functions | |||
| How to substitute a comma with a newline char using Replace. | Excel Discussion (Misc queries) | |||
| Replace Error with a Dash | Excel Worksheet Functions | |||
| Count comma separated numbers, numbers in a range with dash, not t | Excel Discussion (Misc queries) | |||
Hybrid Mode
