+ Reply to Thread
Results 1 to 6 of 6

remove special charactors from data

  1. #1
    Forum Contributor
    Join Date
    06-20-2007
    Location
    Buckeye, AZ
    MS-Off Ver
    365
    Posts
    268

    remove special charactors from data

    I'm trying to purge unnecessary characters from 40K lines of data.
    The characters I need to remove are always at the front of the line but vary in length and type.
    Here are a few examples:
    501-* EASED OR
    1366-DECISIONS
    5874-*2 or 3
    Some are "-*", other are just "-".
    I need to remove everything from the front including the dash and/or asterisk.
    Is this possible?
    Any suggestions would be greatly appreciated!
    Sick
    Last edited by sick stigma; 01-19-2020 at 01:56 PM.

  2. #2
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,133

    Re: remove special charactors from data

    Did you try Find-Replace tool in Excel ?

    Find what >>>> ~*

    Replace >>>>> leave empty


    .
    Last edited by Haluk; 01-19-2020 at 11:52 AM.

  3. #3
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: remove special charactors from data

    If you use the Find and Replace tool be sure to precede the asterisk with a tilde ~*

  4. #4
    Forum Contributor
    Join Date
    06-20-2007
    Location
    Buckeye, AZ
    MS-Off Ver
    365
    Posts
    268

    Re: remove special charactors from data

    what if there are multiple * in the line and I only want to remove to the first *? Also, how would this work for lines that do not have the *?
    Sick

  5. #5
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,133

    Re: remove special charactors from data

    If the string is in cell A1, may be formula like this;

    Please Login or Register  to view this content.
    You may need to change ";" with "," according to your regional settings.

    .

  6. #6
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: remove special charactors from data

    Or rather =Substitute(SUBSTITUTE(A1,"~*","",1),"-",1)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Remove Special Character(s)
    By joshag in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-26-2019, 12:28 PM
  2. Remove special characters using VBA
    By renjer.ranjeet in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-22-2018, 07:40 AM
  3. Reformat time data into decimal and remove excess charactors from field
    By tracierom in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-17-2014, 02:20 PM
  4. [SOLVED] how remove all special characters except # , & - .
    By Latha Mani in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 01-13-2014, 07:14 AM
  5. Replies: 3
    Last Post: 02-25-2013, 11:43 PM
  6. Remove Special Characters
    By bkatzman in forum Excel General
    Replies: 10
    Last Post: 04-13-2010, 11:51 AM
  7. Replies: 3
    Last Post: 07-17-2006, 02:20 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1