I want to eliminate the first character in several lines

  • Thread starter Thread starter ccdhofer
  • Start date Start date
C

ccdhofer

I want to eliminate the first character in several lines, but leave the
remaining characters. For instance, I use Excel as a "log book" and I have a
column that contains chronological numbers that, currently all begin with the
letter "S". (Example S7145A, S7145B, S7146A, etc.) I want to eliminate the
"S", but leave the remaining four digit number, with ending letter intact.
Considering my log bog is nearly 13500 lines long, I want to find a quick way
to do this. I am thinking of using Find and Replace, somehow, but cannot
figure out how to do, without completely clearing the cell. I am using Excel
2007.
 
If all are six characters long and in Col A then enter this in B1 and copy down
=RIGHT(A1,5)

if they are of different lenght then use
=RIGHT(A1,LEN(A1)-1)
 
try
=MID(A2,2,5)

this assume that you always have 6 characters and you want the last 5

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

I am an ordinary user trying to assist another

Thank You

cheers, francis
 
Worked like a charm - only thing I had to do was to copy and paste my numbers
up one row, as they all shifted down one by having to put the formula in. I
do happen to have numbers of all different length, so thanks for including
that info. I would have never came up with that, as I am only a casual user,
so THANK YOU!!!
 
I didn't try this, as I have differing numbers of characters, but thanks
anyway!!! The previous post got me to where I needed to be - but thanks for
taking the time!
 
You are most welcome...

everyone starts as a novice... the joy of Excel (and other software) is that
you discover new and better ways to do things even after years of using it...
 
Hi

Thanks for the feedback. if you do have different length of characters
and want to return from the 2nd characters onward, try

=MID(A2,2,LEN(A2))
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

I am an ordinary user trying to assist another

Thank You

cheers, francis
 
Third parameter in MID is no. of characters to be extracted and not the
position of the last character... so it should be
=MID(A2,2,LEN(A2)-1)
to exclude the first character...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top