+ Reply to Thread
Results 1 to 6 of 6

How to remove all leading and trailing colons (;) from a text?

  1. #1
    Registered User
    Join Date
    11-27-2015
    Location
    Toronto, ON, Canada
    MS-Off Ver
    2010
    Posts
    7

    Post How to remove all leading and trailing colons (;) from a text?

    I have multiple data which contains different quantities of leading and trailing colons ( as well as colons ( inside the alphanumeric text. I need to remove the leading and trailing colons ( from the text; not from the middle. Some of my data are as follows:

    ;;;;915531156;898520523;;;;;;;
    28tgi11148;954419610;;;;;
    542778022;8xtra02;824291991;;;;
    ;;769982422;550004568;640455606;458632786;;
    ;769982422;640455606;;;;

    I would like to get the output as follows:

    915531156;898520523
    28tgi11148;954419610
    542778022;8xtra02;824291991
    769982422;550004568;640455606;458632786
    769982422;640455606

    Please advice.

    Thanks in advance for all your co-operation.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: How to remove all leading and trailing colons (;) from a text?

    Welcome to the board.

    This is not rigorous, but works for data like your examples.

    Row\Col
    A
    B
    C
    1
    ;;;;915531156;898520523;;;;;;; 915531156;898520523 B1: =SUBSTITUTE(TRIM(SUBSTITUTE(A1, ";", " ")), " ", ";")
    2
    28tgi11148;954419610;;;;; 28tgi11148;954419610
    3
    542778022;8xtra02;824291991;;;; 542778022;8xtra02;824291991
    4
    ;;769982422;550004568;640455606;458632786;; 769982422;550004568;640455606;458632786
    5
    ;769982422;640455606;;;; 769982422;640455606
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    11-27-2015
    Location
    Toronto, ON, Canada
    MS-Off Ver
    2010
    Posts
    7

    Re: How to remove all leading and trailing colons (;) from a text?

    Thank you very much shg for your help, it works perfectly. Have a wonderful Thanksgiving weekend.

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: How to remove all leading and trailing colons (;) from a text?

    You're welcome.

  5. #5
    Registered User
    Join Date
    11-27-2015
    Location
    Toronto, ON, Canada
    MS-Off Ver
    2010
    Posts
    7

    Re: How to remove all leading and trailing colons (;) from a text?

    How can I mark this post as SOLVED?

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: How to remove all leading and trailing colons (;) from a text?

    Everything you need to know is explained in the forum rules. Link in the main menu bar.

+ 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. Remove leading and trailing spaces in each new line break in cell
    By halo12 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-29-2015, 07:10 AM
  2. [SOLVED] Remove leading and trailing spaces in a cell?
    By LF_CC in forum Excel General
    Replies: 4
    Last Post: 12-05-2013, 03:12 PM
  3. Remove leading, trailing & non-breaking spaces on a worksheet
    By Kennethc in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-06-2013, 07:03 PM
  4. To remove trailing text containing G00 in all rows.
    By excelk in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-14-2013, 04:40 AM
  5. how do I remove empty spaces trailing a text string?
    By Need_Help in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  6. how do I remove empty spaces trailing a text string?
    By Need_Help in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-06-2005, 12:05 AM
  7. [SOLVED] how do I remove empty spaces trailing a text string?
    By Need_Help in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-06-2005, 08: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