+ Reply to Thread
Results 1 to 6 of 6

Split text by character

  1. #1
    Registered User
    Join Date
    05-15-2015
    Location
    Sofia, Bulgaria
    MS-Off Ver
    2007
    Posts
    26

    Split text by character

    Hello, is there a way to split text by character, like text to colum, for example by " # "
    If i have: My # Name # is # Stephan
    to split each word in different cell.

  2. #2
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,412

    Re: Split text by character

    Did you try "Text to Column" using " # " as the delimiter ?
    GC Excel

    If this post helps, then click the star icon (*) in the bottom left-hand corner of my post to Add reputation.

  3. #3
    Registered User
    Join Date
    05-15-2015
    Location
    Sofia, Bulgaria
    MS-Off Ver
    2007
    Posts
    26

    Re: Split text by character

    Yes, but i need to do this with formula. Exactly like text to column but with formula

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Split text by character

    Try this...

    A1 = My # Name # is # Stephan

    Enter this formula in B1:

    =TRIM(MID(SUBSTITUTE(" # "&$A1," # ",REPT(" ",255)),
    COLUMNS($B1:B1)*255,255))

    Copy across until you get blanks.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Split text by character

    =TRIM(MID(SUBSTITUTE($A1,"#",REPT(" ",LEN($A1))),(COLUMNS($B1:B1)-1)*LEN($A1)+1,LEN($A1)))

    IF YOU HAVE YOU DATA IN CELL A1 TRY ABOVE FORMULA

    Edit the range as per your need
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  6. #6
    Registered User
    Join Date
    05-15-2015
    Location
    Sofia, Bulgaria
    MS-Off Ver
    2007
    Posts
    26

    Re: Split text by character

    Thank you very much! It works great!

+ 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] Split a string after last alphabetic character
    By Glenn Kennedy in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 12-30-2014, 03:44 AM
  2. Split cells long text with Max 40 character to multiple row using Macro
    By chee1012 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-31-2012, 10:10 AM
  3. Split text box by Return Character
    By mccrimmon in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-02-2011, 01:46 PM
  4. Excel 2007 : How to split to column at character
    By PBMClubber in forum Excel General
    Replies: 6
    Last Post: 11-22-2010, 07:50 AM
  5. importing undelimited text file data, character-by-character
    By The Mos$ in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-26-2005, 07:10 AM

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