Remove leading Apostrophe (‘) in Excel Data from Numbers and Text – How To

Remove leading Apostrophe (‘) in Excel Data from Numbers and Text – How To

This question is asked by one of the member at our facebook community. He was stuck in common problem. Leading apostrophes. This is often the case if data is extracted from a famous business application Lotus Notes. If the data is copied or extracted to Excel then these unwanted characters interferes with Excel’s normal functioning. So let’s get rid of them and its quite easy too.

Download Example Workbook

Download this example excel workbook in order to practice the methods, techniques and concepts that we learn in this tutorial.

Use Find and Replace – A Misfire!

Dear member tried to use find and replace option that I discussed earlier through which we were able to replace particular string from Excel data with the desired result. Here he tried to replace apostrophe ( ‘ ) with blank and so left “Replace with” field empty whereas in “Find what” field he mentioned the apostrophe. But on hitting find or replace button Excel returned an error that it cannot find the required data. This surely is perplexing situation that our eyes can literally see it how Excel has went too dumb to find it? Well there is a reason that actually saved Excel from an utter disgrace in your eyes 🙂

Why Excel can’t find apostrophe?

Excel is designed to be compatible with IBM Lotus Notes and since beginning as, to the extent I have read, its based on Lotus Notes framework so it has many of its features as well. In Lotus Notes leading apostrophe signifies left aligned data. If you go to Excel Options > Advanced > Lotus Compatibility > Check select transition navigation keys > OK and return to your data you will see that Excel will show the selected cell to be left aligned. If you change to center the sign will change to ^ (carat) and if aligned right it will change to ” (quote).

So basically these characters are indicators and are not really part of the text. In other words they don’t really exist and so Excel treat them such as well but somehow messes up the data. And even after turning the compatibility option on, Excel still can’t find it.

Bring the Specialist! – Use Paste Special

Step 1: Select the data using mouse or holding down Ctrl+Shift keys and pressing directional keys.

Step 2: Copy the data either by right clicking or using a shortcut Ctrl+C

Step 3: Move to a different column within same worksheet or another worksheet. Hit Alt+Ctrl+V (this invokes paste special dialogue box) select values and click OK. Now you have the data without leading apostrophes.

Step 4: Apostrophe still there? Go to File > Options > Advanced tab > Scroll down to Lotus Compatibility > Uncheck “Transitional navigation keys” box > Click OK button.

In another column? Seriously???

Well… yeah! For some reason if you try to paste the values in the same column where the data actually is, it does not work (most of the time). We are able to convert formulas with their resultant values very easily within same column but somehow this does not work. Reason? I don’t know that yet. If anyone of you know why is this please share with me as well in the comment box below.

Personal Favourite – Use Text-to-Column

In many cases you can’t have the liberty to use helper or additional column to do the workings. In such cases:

1. pray that your data is number only like digits, numbers, time or date etc
2. use text to column feature in excel

Step-by-step

Step 1: Select the data using mouse or keyboard

Step 2: Go to Data Tab > Data Tools group > Text to column button

Step 3: Once the dialogue box appears. Do nothing and click Finish button.

Super quick! Your data will convert to numbers or dates in the correct format. Note however that this does not work with Text i.e. if text follows leading apostrophe then this method don’t work at all.

Use formula – DATEVALUE / VALUE etc

Correcting Dates

We can also use formulas to convert numbers stored as text in numbers actually to perform calculations. For example if it is a date then you can use DATEVALUE function to convert it to workable date.

For example to convert dates in column B to correct format follow these steps:

Step 1: In cell E2 put this formula: =DATEVALUE(B2) and press Enter.

Step 2: You will now have some kind of a number. To format it as proper Date go to Home Tab > Number group > From the drop down select either Short or Long date.

You can achieve the same by Hitting Ctrl+1 as well and in the dialogue box in the left select Date. From the options at the right side select the format that best suits you and click OK.

Step 3: Drag the fill handle to the end of range to get the correct dates in the rest of the column.

Correcting numbers (numerical values)

Similarly if you have numbers, date or time you can use VALUE function to convert it to actual state and perform calculations.

To convert time to correct format which is in column C, follow these steps:

Step 1: In cell F2 put this formula: =VALUE(C2) and press Enter key

Step 2: You have to format the cell in Time format just like you did for Date above. To do so use the home tab or invoke number format dialogue box by hitting Ctrl+1.

Remember, you need an additional column in this case i.e. if you want to apply formula. Once you have the text converted to numbers you can replace the original data with the correct one or use the new column for calculation purposes.

Should we really fret about apostrophe in Text?

In all the above situations you must have seen that all the examples are related to numbers. But what if we have text following leading apostrophe?

Well the question I would ask myself is do I really need to worry about? I know its a lazy-brain attitude but lets ask it. Well the answer is NO. At least for me it caused no problem. To prove this to myself I did a logical test and surprisingly if you compare the same text in two cells one with and the other without apostrophe test comes TRUE.

So that means it really is not a problem at least in case of formula and function use. I don’t know much about its consequences in VBA yet but to the extent I pitched it was no pain for me. May be that is the reason Microsoft also left the apostrophe for text unattended.

If you still insist that no you want to have it removed then we can at least hide it. By going to Excel options uncheck “Transition navigation keys” box.

But sometimes before this works on the text you might have to use the paste special approach discussed above and you will get rid of stubborn apostrophe or at least it does not show its face.

Liked it? Pin it!