remove the first letter in each cell in a column

[AnnaPanna]

Hi All,

I have a list with a column where all cells begins with the letter D. I would like to remove that D from all cells. For instance;

Dxxx1
Dxxx2
Dxxx3
Dxxx4

Should be;

xxx1
xxx2
xxx3
xxx4

How can I do that in a fast and easy way?

Thank you!

 

[xld]

With Activesheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For i = 1 To LastRow

.Cells(i, "A").Value = Right(.Cells(i, "A").Value, Len(.Cells(i, "A").Value)-1)
Next i
End With

 

[shyrath]

if the string is in A1 use this in B1

=RIGHT(A1,LEN(A1)-1)

 

[AnnaPanna]

Is this the easiest way...

Im not familiar with those formulas. Can I do it in another way?

 

[AnnaPanna]

Super Shy. THanks!

 

[Mr. Walnuts]

Your easiest method is what Shyrath said;
find an empty cell, type the following formula in it, then once its in there, then you can put the mouse cursur over the bottom right corner of that cell and drag that same formula down and it will auto-fill all the cells below it with the formula except it will reference the second, third, forth, etc cell below the one in the original formula.

=RIGHT(A1,LEN(A1)-1) <--- replace "A1" with the name of the first cell in the column.

Once you drag the formula down (drag it from the box in the lower right corner of the cell), the next cell will autofill with the formula:
=RIGHT(A2,LEN(A2)-1)

so on and so forth

It is defintely the easiest way without using VBA; plus it eliminates needing to type the formula in each box, only the first one... then drag it down to autofill

 

[shyrath]

Super Shy. THanks!

welcome, but it´s the same formula as what Mr Walnuts said, only his is the Visual Basic method, which means you can do it by running a macro / button. Which is, by far, actually the quickest way and doesn´t need an extra column.

 

[pgc01]

Hi

A simple way to remove the first letter from the cells in a column is to use Text to Columns and skip the first character.

Notice that this method does not use formulas nor vba and does not need an auxilliary column.

 

[hardeep.kanwar]

If Its Always D

CTRL+H

Find What -D
Replace With -Blank
Then< Ok

Hardeep kanwar

 

[shyrath]

If Its Always D

CTRL+H

Find What -D
Replace With -Blank
Then< Ok

Hardeep kanwar

I´m afraid that would also remove D if it´s in the middle of a word. Not just the first one.

I think pgc01 wins with the text to column one ( i didn´t know that would work! ) I tested that, and you could also use "D" as the delimiter. (if you have the room for it, as it will try and fill to the next column ofcourse).