CODEWITHSUNDEEP
excelexcel-formula
Ravichandran Jothi
Ravichandran Jothi

Reputation: 3066

How to remove the first letter in each cell in a column in excel?

I have column in excel like below,

'04-Feb-01
'04-Mar-01
'08-Apr-01
'06-May-01
'03-Jun-01
'08-Jul-01
'05-Aug-01

I want to remove the character ' in all cells how can i do that?

I already tried with =RIGHT(A1,LEN(A1)-1) this one.. not working

Upvotes: 1

Views: 9536

Answers (2)

Dave Sexton
Dave Sexton

Reputation: 11188

When you enter data into a cell, Excel will try and convert it to the correct type (is it text, a number or a date) The single quote is Excel's way of saying treat what follows as text. It comes in handy when entering things like telephone numbers which Excel might think is a number and would not display leading zeros. In your the example the actual value in the cell is the text without the single quote, you can check this by copying a cell and then pasting into notepad and there would be no quote in the result.

Why do you want to remove the quote? If it is because you want the cells to contain a date rather than text you can convert it using the DATEVALUE function or by using copy and paste special values only .

Upvotes: 1

HPD
HPD

Reputation: 26

Convert the text to a date using DATEVALUE. Then apply a date format to your liking.

Upvotes: 1

Related Questions