Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Replacing Dashes with Periods.
Written by Allen Wyatt (last updated August 25, 2021)
This tip applies to Excel 97, 2000, 2002, and 2003
If you have a need to normalize the appearance of your data, you might want to replace any dashes in a text string with periods. For instance, if you have a phone number such as "123-555-1212" you might want to change it to "123.555.1212". This is easy to do using the SUBSTITUTE function:
=SUBSTITUTE(A7,"-",".")
The result is that any dashes appearing in the string in cell A7 are replaced with periods.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2995) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Replacing Dashes with Periods.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
Excel provides a variety of tools that allow you to perform operations on your data based upon the characteristics of ...
Discover MoreCalculating an average of a group of numbers is easy. What if you want to exclude a couple of the numbers from the group ...
Discover MoreThe FIND and SEARCH functions are great for finding the initial occurrence of a character in a text string, but what if ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2017-11-30 05:55:12
Sankar
Great help, thanks a lot Allen :-)
2017-03-18 22:26:27
sunil
I want add dash in a coloumn which consists of clients name ..
clients name is as follows
jidf
Baysee
carparl
it should looks like
JI_DF
BAYS_EE
CARPA_RL
after last two words i need to add an dash.
Plese help me to sort it out,
Regards
Sunil
2014-11-18 09:08:32
JMJ
@Carlos
You have to have your cells formated as "text" before making the replacement, in order to prevent Excel to make "intelligent" assumptions...
2014-09-02 14:36:05
Carlos
I am trying something similar to Jasmin, but instead I am trying to change periods into dashes "1234-12.1" to "1234-12-1". When I use the search and replace in Excel 2013 it changes the format to a date "1234/12/1". Is there a way that I can do this easily without having to do it one by one? VB Posssibly?
2012-06-25 09:58:14
awyatt
Jasmin,
If you change 123-4500 into 123.4500, then Excel treats it as a number where the trailing zeroes are not significant. If you want those zeroes to be displayed, format the cells so that four digits after the decimal point are always displayed.
-Allen
2012-06-21 13:25:50
Jasmin
The find-replace doesn't always work to replace dashes with periods. I have a number that ends in 1 or 2 zeros (ex. 123-4500), and if you change the dashes into periods, its REMOVES the zeros at the end if the number ends in 1 or more zeros. How do I keep all zeros in tact??? PLEASE HELP! Thanks.
2011-12-06 12:09:06
allan
or you may just use the find and replace command. select all cells that you want to replace. select the find and replace command. put the dash character "-" in the find what box, and in the replace with box, put the period character ".". then select replace all. thanks.
2011-11-27 14:25:29
Joel
Hello.
This is also possible using the Find & Replace feature (ctrl+H). Saves having to type in a formula and copy/paste/values.
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Please Note:
This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here:
Comments