Results 1 to 8 of 8

Thread: Remove * character

  1. #1
    Board Regular
    Join Date
    Nov 2004
    Posts
    940
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Remove * character

    I hope that this is is not too dopey a question.

    I receive a sheet of 1000 plus records frequently where in column A each record is part of an English post code with an asterisk at the end like AB1 or KT32*. However many characters there is always a * character.
    I would like to be able to remove the * and then add it back after I have finished.
    As ever many thanks for any help you can provide.

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,109
    Post Thanks / Like
    Mentioned
    469 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Remove * character

    To remove them, select col A > Ctrl H > findwhat: ~* > leave replace with blank > OK
    To put them back in use
    Code:
    Sub Addstar()
       With Range("A2", Range("A" & Rows.count).End(xlUp))
          .Value = Evaluate("if({1}," & .Address & "&""*"")")
       End With
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,293
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Remove * character

    Make a copy of column A and paste to a different worksheet
    In column B of existing sheet use formula "=LEFT(A2,LEN(A2)-1)"
    In column C of existing sheet use formula "=RIGHT(A2,1)"
    Copy columns B and C and pastespecial as values

    Now you can use columns B and C to re-create your original column A or from the backup you copied to as first step.


  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Remove * character

    Quote Originally Posted by lapta301 View Post
    I would like to be able to remove the * and then add it back after I have finished.
    After you have finished what?

    Can you explain why you want/need to remove the * in the first place?

    And why do you then want/need to add it back 'after you have finished'?

    Can you give us some sample data and explain what you are actually trying to do?
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  5. #5
    Board Regular
    Join Date
    Nov 2004
    Posts
    940
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Remove * character

    Peter I just have a need to do some work on the internal post codes and then reset them back to the format that they arrived in. The example in my post are what I receice

  6. #6
    Board Regular
    Join Date
    Nov 2004
    Posts
    940
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Remove * character

    Fluff
    Thank you that works a treat I now recall from many years ago the tilde character,
    Again my sincere thanks

  7. #7
    Board Regular
    Join Date
    Nov 2004
    Posts
    940
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Remove * character

    JackDanice
    Likewise thank you for taking an interest and your solution also works a treat

    Regards to you all from a rather gloomy wet Southern England

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,109
    Post Thanks / Like
    Mentioned
    469 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Remove * character

    Glad we could help & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •