Select the range of cells that you want to convert (remove apostrophes from). So, if you have a cell containing the full name of a person separated by a space, you can use the Text-to-Columns feature to separate the cell into two cells, one containing the first name and the other containing the last name.ĭuring this process of splitting cells, the Text-to-Columns feature also converts numeric values to numbers, date values to dates, and all remaining values to text.Īs such, this feature provides a really clever and quick way for you to convert all your apostrophe delimited cells back to their number or date formats. The Text-to-Columns feature is basically meant to help you split text contents in cells into multiple columns. This is the most commonly used method to remove leading apostrophes in Excel. Using the Text-to-Columns Feature to Remove Apostrophe Note: In case you’re thinking you can use Find and Replace to find the apostrophe symbol and replace it with a blank, it doesn’t work. However, if there’s a whole column of cells that you need to work with, then here are three ways to work around the problem: If it’s just one or two cells, you can select the cell and remove the apostrophe to bring it back to normal. 3 Ways to Remove Leading Apostrophes in Excel If you don’t preface the number with an apostrophe, Excel will automatically format the number by removing all the leading zeros. 
For example, you might have an ID number as: Sometimes you might want leading zeros in your numerical values to remain. You can do so by simply adding a leading apostrophe. If you don’t want this to happen and you just want to display the text as ‘Jan-01’, then you need to somehow tell Excel that you want this to be considered as a text value, not as a date. Go ahead and try it… It’s quite frustrating! If you specify something like Jan-01, Excel will automatically convert it to a date and format it according to your global date format (like ). So, even if the cell contains a number or date, Excel will treat it as text.īut why would anyone want Excel to treat numbers or dates as text? Leading apostrophes forces excel to treat the cell’s contents as a text value.
You expect to get a sum of 1670, but the apostrophes have caused the SUM function to ignore the first 4 numbers and only consider the last number, 50. When you apply a calculation to this column, for example, the SUM function, notice it does not give the desired result.
0 kommentar(er)
