+ Reply to Thread
Results 1 to 3 of 3

Text to columns based on just first delimiter

  1. #1
    Registered User
    Join Date
    02-22-2013
    Location
    Pakistan
    MS-Off Ver
    2016
    Posts
    95

    Text to columns based on just first delimiter

    I have a data as sampled below:

    1001- House No. 32 - Paris France
    33 - 98 A Germany
    86- P-13 St #1 England


    I want to apply "Apply text to columns" so that data is divided in just two columns.

    1) Data before first "-"
    2) Data after the first "-"

    The difficulty I am facing is

    1) If I use "-" as delimiter, the cell has more than one delimiter converting it into more than required number of cells. (more than 2)
    2) If I extract numeric values, it picks up other numbers too. Like House no or street no.
    3) The data I need to extract (First Numeric value*) does not have fixed length.

    * 1001, 33 and 86 in case of sample data.

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 V 2503
    Posts
    13,715

    Re: Text to columns based on just first delimiter

    Try using Flash Fill. I just did it at my end with your sample.
    Dave

  3. #3
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 V 2503
    Posts
    13,715

    Re: Text to columns based on just first delimiter

    Or try this parsing formula in B1 filled down and across.
    Formula: copy to clipboard
    Please Login or Register  to view this content.




    A
    B
    C
    1
    1001- House No. 32 - Paris France
    1001
    House No. 32 - Paris France
    2
    33 - 98 A Germany
    33
    98 A Germany
    3
    86- P-13 St #1 England
    86
    P-13 St #1 England

+ 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] Text to columns with hard (carriage) return delimiter between emails
    By WilliamWelch in forum Excel General
    Replies: 2
    Last Post: 10-31-2018, 07:41 PM
  2. [SOLVED] Text to Columns - no delimiter
    By SharpL in forum Excel General
    Replies: 1
    Last Post: 10-08-2014, 06:43 AM
  3. Replies: 10
    Last Post: 07-09-2014, 05:44 PM
  4. Change default Text-to-Columns Delimiter to COMMA ?
    By KK_Wenz in forum Excel General
    Replies: 1
    Last Post: 02-11-2014, 04:06 PM
  5. Delimiter on Text to Columns Issue
    By Luck in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-18-2011, 06:32 AM
  6. Text to columns VBA code to change delimiter if the cell value is in array
    By dopple in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-26-2008, 09:25 AM
  7. How to use text to columns with a vertical bar as the delimiter?
    By carey123 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-04-2007, 04:58 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