+ Reply to Thread
Results 1 to 8 of 8

how to find special character & replace with blank

  1. #1
    Valued Forum Contributor mohan.r1980's Avatar
    Join Date
    09-18-2010
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2010 (windows7)
    Posts
    729

    how to find special character & replace with blank

    Hello Sir,

    i have names of companies with comma, dot, ?, &
    i want to replace with blank only that character

    how do i achieve this in excel formula.
    Last edited by mohan.r1980; 08-31-2016 at 07:46 AM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: how to find special character & replace with blank

    One way:

    =TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,","," "),"?"," "),"."," "),"&"," "))
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    08-29-2016
    Location
    Bucharest
    MS-Off Ver
    2013
    Posts
    1

    Re: how to find special character & replace with blank

    Hi,

    CTRL + H and replace any special character (*?>":...) using "~" before
    ~, or ~. or ~? or ~?

  4. #4
    Valued Forum Contributor mohan.r1980's Avatar
    Join Date
    09-18-2010
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2010 (windows7)
    Posts
    729

    Re: how to find special character & replace with blank

    Dear Glenn Kennedy,

    thanks to reply
    if suppose name contain !, # or another special character then every time i have change the formula manualy.
    is there is a way to find out the special character and replace it with blank??

  5. #5
    Valued Forum Contributor mohan.r1980's Avatar
    Join Date
    09-18-2010
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2010 (windows7)
    Posts
    729

    Re: how to find special character & replace with blank

    Hello 20rin

    thanks to reply

    is it possible in formula??

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: how to find special character & replace with blank

    Here's a possible VBA solution for you. It is sufficiently flexible to allow you to replace characters with a space, a blank, or any other character.

  7. #7
    Valued Forum Contributor mohan.r1980's Avatar
    Join Date
    09-18-2010
    Location
    Mumbai, India
    MS-Off Ver
    Excel 2010 (windows7)
    Posts
    729

    Re: how to find special character & replace with blank

    thank you very much Glenn Kennedy

    its working fine.

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: how to find special character & replace with blank

    You're welcome and Thanks for adding Reputation.

+ 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. Replace special character
    By winmaxservices in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 08-18-2016, 12:48 AM
  2. Need to highlight special character and the cells contains special character
    By dhilipan in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-26-2013, 01:36 AM
  3. How to vlookup a Column contain special character and replace it?
    By miraclesuki in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-22-2013, 05:03 PM
  4. to find and replace the special character in multiple worksheet in a workbook
    By Vinutha in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-11-2012, 11:16 AM
  5. How to replace special character (square box) in cell
    By anilk.zee in forum Access Programming / VBA / Macros
    Replies: 2
    Last Post: 05-21-2012, 03:17 PM
  6. How to replace special character (square box) in cell
    By anilk.zee in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-21-2012, 12:10 PM
  7. [SOLVED] How do I find replace special characters?
    By zzapper in forum Excel General
    Replies: 1
    Last Post: 06-27-2005, 02:05 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