1. Welcome! Please take a few seconds to create your free account to post threads, make some friends, remove a few ads while surfing and much more. ClutchFans has been bringing fans together to talk Houston Sports since 1996. Join us!

  2. ROCKETS GAMEDAY
    We get another Amen Thompson vs. Tyler Herro matchup as the Rockets take on the Heat in Miami Saturday. Come join Dave and Ben with Paulo Alves as they recap the game.

    LIVE! ClutchFans on YouTube
Welcome ClutchFans Live

How to remove asterisks from a column in Excel

Discussion in 'BBS Hangout' started by Luckyazn, Jul 25, 2014.

Tags:
  1. Luckyazn

    Luckyazn Member

    Joined:
    Jun 23, 2003
    Messages:
    4,375
    Likes Received:
    68
    I have a spreadsheet that under a column (Account Number) has two asterisks in front of the 4 digits (to hide the full account number)

    How do I remove all the asterisks from this column to only show the last four digits?

    Thanks,
     
    #1 Luckyazn, Jul 25, 2014
  2. pickymen

    pickymen Member

    Joined:
    Oct 28, 2002
    Messages:
    450
    Likes Received:
    10
    Is the format the same for all the rows, i.e. **1234?

    If so, you may try: text to column > fixed width and split the asterisks from the numbers.
     
    #2 pickymen, Jul 25, 2014
  3. HamJam

    HamJam Member

    Joined:
    Mar 19, 2011
    Messages:
    2,615
    Likes Received:
    560
    Just highlight the column in question, control f, find **, replace with nothing. Boom, all set.
     
    #3 HamJam, Jul 25, 2014
  4. Luckyazn

    Luckyazn Member

    Joined:
    Jun 23, 2003
    Messages:
    4,375
    Likes Received:
    68
    When I did that all the number disappeared from the column
     
    #4 Luckyazn, Jul 25, 2014
  5. HamJam

    HamJam Member

    Joined:
    Mar 19, 2011
    Messages:
    2,615
    Likes Received:
    560
    Really? That's strange. Are all the numbers like this: "**1234" ? Perhaps try copying the column and then pasting it into a new column, but do paste special "value", and then try to control f and replace all the "**" with nothing.
     
    #5 HamJam, Jul 25, 2014
    1 person likes this.
  6. DonkeyMagic

    DonkeyMagic Member
    Supporting Member

    Joined:
    May 22, 2006
    Messages:
    21,618
    Likes Received:
    3,505
    =right(cell, LEN(cell)-# of characters to remove)
     
    #6 DonkeyMagic, Jul 25, 2014
    1 person likes this.
  7. JayZ750

    JayZ750 Member

    Joined:
    May 16, 2000
    Messages:
    25,432
    Likes Received:
    13,390
    to parrot above, if

    A1 = **1234

    Then in B1, do =right(a1,4)

    Right pulls from the right the last [x] items of the cell you reference.
     
    #7 JayZ750, Jul 25, 2014
  8. Cohete Rojo

    Cohete Rojo Member

    Joined:
    Oct 29, 2009
    Messages:
    10,344
    Likes Received:
    1,203
    Control + h

    You're welcome.
     
    #8 Cohete Rojo, Jul 25, 2014
  9. JuanValdez

    JuanValdez Member

    Joined:
    Feb 14, 1999
    Messages:
    35,457
    Likes Received:
    15,933
    I've had that happen. It's not supposed to, but Excel gets wonky sometimes. Donkey's and Jay's solution should work. You might also be able to copy everything, open a new workbook and paste values (be sure it's values only, not everything), and then do the Find/Replace trick on the new file. It might still not work though.
     
    #9 JuanValdez, Jul 25, 2014
  10. pugsly8422

    pugsly8422 Member

    Joined:
    Mar 19, 2002
    Messages:
    3,265
    Likes Received:
    349
    Pretty much what DonkeyMagic said.....

    =right(a1,4)

    That should take the 4 characters furthest to the right in the cell.
     
    #10 pugsly8422, Jul 25, 2014
  11. bigtexxx

    bigtexxx Member

    Joined:
    Jun 12, 2002
    Messages:
    26,980
    Likes Received:
    2,365
    one option is to click on the cell, delete the asterisks, and then do the same for all the other cells
     
    #11 bigtexxx, Jul 25, 2014
  12. TheRealist137

    TheRealist137 Member

    Joined:
    Jan 27, 2009
    Messages:
    37,035
    Likes Received:
    24,491
    I heard alt+f4 removes all of the asterisks from your screen
     
    #12 TheRealist137, Jul 25, 2014
  13. FTW Rockets FTW

    FTW Rockets FTW Member

    Joined:
    Jun 23, 2011
    Messages:
    27,724
    Likes Received:
    21,397
    It's simple. a 5 year old knows this.

    Go to the cell, click right after the 2nd asterisk and then hit backspace twice. Then repeat this process
     
    #13 FTW Rockets FTW, Jul 25, 2014
(You must log in or sign up to post here.)

Share This Page