Removing letters from cells

Discussion in 'Excel Beginners' started by Quilp, Sep 17, 2013.

  1. Quilp

    Quilp Guest

    I have a column where the cells contain V11, C139, D27 etc. I need t
    strip the letters out so that I can apply the MAX formula to the entir
    column. The entries are also entered as text so I will need to remov
    the apostrophe. I have only just discovered the text/value toggle so
    unwittingly, I have been entering text instead of "value" (number).
    have formatted the column as "number" and have tried highlighting th
    cells and toggling to "AUTO" but the damned apostrophe remains.
    Anyone out there who can help


    --
    Quilp
     
    Quilp, Sep 17, 2013
    #1
    1. Advertisements

  2. Quilp

    Claus Busch Guest

    Hi,

    Am Tue, 17 Sep 2013 09:46:47 +0100 schrieb Quilp:

    > I have a column where the cells contain V11, C139, D27 etc. I need to
    > strip the letters out so that I can apply the MAX formula to the entire
    > column. The entries are also entered as text so I will need to remove
    > the apostrophe. I have only just discovered the text/value toggle so,
    > unwittingly, I have been entering text instead of "value" (number). I
    > have formatted the column as "number" and have tried highlighting the
    > cells and toggling to "AUTO" but the damned apostrophe remains.


    if your values are in column B then insert an empty column right from B
    and enter following formula:
    =--RIGHT(B1,COUNT(RIGHT(B1,COLUMN(1:1))*1))
    and enter the array formula with CTRL+Shift+Enter and copy down. Select
    this column and copy it, then insert => Paste special => Paste values
    and delete column B.
    If formatting doesn't work click on column header, format the column
    with your wished format and choose Data => TextToColumns => Fixed Width
    => Finish


    Regards
    Claus B.
    --
    Win XP PRof SP2 / Vista Ultimate SP2
    Office 2003 SP2 /2007 Ultimate SP2
     
    Claus Busch, Sep 17, 2013
    #2
    1. Advertisements

  3. Quilp

    Quilp Guest

    if your values are in column B then insert an empty column right from B
    and enter following formula:
    =--RIGHT(B1,COUNT(RIGHT(B1,COLUMN(1:1))*1))
    and enter the array formula with CTRL+Shift+Enter and copy down. Select
    this column and copy it, then insert => Paste special => Paste values
    and delete column B.
    If formatting doesn't work click on column header, format the column
    with your wished format and choose Data => TextToColumns => Fixed Width
    => Finish


    Regards
    Claus B.
    --
    Win XP PRof SP2 / Vista Ultimate SP2
    Office 2003 SP2 /2007 Ultimate SP2

    I am sorry Claus but I still can't get this to work - no doubt due to m
    lack of experience of worksheets.
    However, I have now done it the hard way - I have deleted both the lette
    and the apostrophe from each of the 400 or so cells, one by one, using th
    delete key. It took me ages but now I have been able to use the Ma
    formula.

    I have saved a copy of my worksheet as an "Exercise Sheet" which I wil
    use to practice on in order to learn more about spreadsheets and
    hopefully, to enable me to follow forum members suggestions in th
    future.


    --
    Quilp
     
    Quilp, Sep 18, 2013
    #3
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. trainspotter20

    How do i make text in one column all capital letters

    trainspotter20, Sep 28, 2004, in forum: Excel Beginners
    Replies:
    3
    Views:
    68
    David McRitchie
    Sep 28, 2004
  2. Loi

    Big Letters

    Loi, Sep 30, 2004, in forum: Excel Beginners
    Replies:
    3
    Views:
    86
    David McRitchie
    Oct 3, 2004
  3. cfitz

    how do i represent numbers with letters

    cfitz, Feb 4, 2005, in forum: Excel Beginners
    Replies:
    4
    Views:
    98
    Harald Staff
    Feb 5, 2005
  4. Alex
    Replies:
    3
    Views:
    86
  5. popabear
    Replies:
    1
    Views:
    72
    Trevor Shuttleworth
    Apr 7, 2005
  6. Kinger
    Replies:
    2
    Views:
    74
    JE McGimpsey
    May 2, 2005
  7. David494

    Change Columns to Letters

    David494, Jul 29, 2005, in forum: Excel Beginners
    Replies:
    5
    Views:
    113
  8. greenstone
    Replies:
    2
    Views:
    122
    Gordon
    Aug 3, 2005
Loading...