+ Reply to Thread
Results 1 to 16 of 16

Script to replace speacial characters with line breaks

  1. #1
    Registered User
    Join Date
    12-05-2013
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    25

    Script to replace speacial characters with line breaks

    hi friends,

    I am new to VBA excel scripting.. I need a script that fulfills the following conditions..

    I have an excel sheet which contains the data as the following


    """To:sara;
    cc: abi; pup; """
    """To: loe, frez;Str, Peo; lrson
    Cc: IT; GS; """
    "

    each cell in the first column contains the same data.. now I need the output as follows

    sara
    abi
    pup
    loe,frez
    str,Peo
    irson
    IT
    GC

    which means the characters ; : " should be replaced with line break.. and the names should be displayed in the second sheet or some other column one by one.. but in the same column..


    could u please help me to do this.. many thanks in advance

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Script to replace speacial characters with line breaks

    Assuming data in col.A
    Please Login or Register  to view this content.
    Edit: Code
    names should be displayed in the second sheet or some other column one by one.
    Last edited by jindon; 12-05-2013 at 11:51 AM.

  3. #3
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Script to replace speacial characters with line breaks

    Hi saranyabi,
    as an option
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    12-05-2013
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Script to replace speacial characters with line breaks

    Hi Jindon,

    tha above code is a good one.. many thanks also some of the data is missing when using the script.. please try the following data and help me to do the same..

    "1st Contact: WI-sara
    ""To: dl it ok leo Basis; [email protected];
    cc: ITpo, ISD;Jay nimen ;Wuiopt Grzz i, Anu Stena, liza martin""
    [email protected]"

    your reply is very important to me as this is the one i was expecting

  5. #5
    Registered User
    Join Date
    12-05-2013
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Script to replace speacial characters with line breaks

    hi nilem,

    many thanks for your response.. also it shows the following error.. there is no data "there is no data found".. kindly assist with me step by step.. as im new to VBA.. thank you...

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Script to replace speacial characters with line breaks

    If this is not what you are expecting, need to see your desired result in a workbook.
    Please Login or Register  to view this content.

  7. #7
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Script to replace speacial characters with line breaks

    Hi Saranyabi,
    please attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.
    View Pic

  8. #8
    Registered User
    Join Date
    12-05-2013
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Script to replace speacial characters with line breaks

    hi nilem,

    i have attached an excel sheet.. inwhich sheet1 contains input and the sheet2 contains the output data.. kindly help me to do this.. many thanks in advance..
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    12-05-2013
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Script to replace speacial characters with line breaks

    Hi jindon,

    its a perfect one.. thanks a lot. this is a better one.. but still some data is missing.. can u find the attachment and help me as needed.. also please find the below input

    """To:sara, abi; Stephan, Peter;[email protected]
    Cc: IT NEED Helwl; Golo; """

    output

    sara, abi
    stephan, Peter
    [email protected]
    IT NEED Helwl Golo

    and your script is working well except the following

    it doesnot takes the string after ; which means in my outpu [email protected]
    is missing..

    please help me with this
    Last edited by saranyabi; 12-06-2013 at 08:13 AM.

  10. #10
    Registered User
    Join Date
    12-05-2013
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Script to replace speacial characters with line breaks

    Hi nilem,

    could u please help me on this.. i have uploaded the excel sheet...

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Script to replace speacial characters with line breaks

    This should do
    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    12-05-2013
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Script to replace speacial characters with line breaks

    Dear jindon,

    this is a perfect one.. and i tried with one exceptional case.. please refer the following

    input:

    """To:rio, martina; tinkil, nio(External);[email protected]
    Cc: ITBS; GOAL; """


    when generating output the quotes have not been deleted in last line "" displays.. Please check... thanks a lot..

  13. #13
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Script to replace speacial characters with line breaks

    Change one line
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    12-05-2013
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Script to replace speacial characters with line breaks

    perfect one jindon.. thats juz awesome.... thanks a lot..

  15. #15
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Script to replace speacial characters with line breaks

    You are welcome and thanks for the rep.

    Can you mark this thread as "SOLVED"?

  16. #16
    Registered User
    Join Date
    12-05-2013
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Script to replace speacial characters with line breaks

    ya sure...

+ 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 comma with new line characters
    By OahuBrew in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-12-2013, 04:05 PM
  2. Difficulty creating a basic line chart without breaks in line plots
    By brharrii in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 04-05-2013, 04:29 PM
  3. [SOLVED] VB Paste.Speacial.values
    By Excelnoub in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-27-2012, 12:23 PM
  4. Adding line breaks by using find and replace
    By soozie in forum Excel General
    Replies: 3
    Last Post: 03-06-2005, 08:06 PM
  5. [SOLVED] Find/Replace carriage return & line feed characters in Excel.
    By Mary Cullen in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-04-2005, 04:06 PM

Tags for this Thread

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