+ Reply to Thread
Results 1 to 3 of 3

How to split text from text string into separate columns...no delimiters!

Hybrid View

  1. 04-08-2014, 09:35 PM #1
    droberts2014
    droberts2014 is offline
    Registered User
    Join Date
    04-08-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    2

    How to split text from text string into separate columns...no delimiters!

    Hello
    I have the cell data as below
    How would I split into a new column the first part which is a date into a new column, then the country and the remainder into separate columns?
    I still want the original data as I need to check that the splits worked well?
    Thanks for any help!

    16.5.90 CH 1671/90-4
    18.10.1991 CH 3056/91-1
    24.07.92 ch 2341/92-2
    30.7.92 ch 2395/92-3
    18.11.92 Us 3533/92-5
    26.5.93PCT 1577/93-0
    9.8.93 CH 2363/93-8
    17.8.93 CH 2445/93-0
    25.1.94ch209/94-6;8.12.94ch3714/94-1
    25.1.94 ch 209/94-6 ; 8.12.94 ch 3714/94-1
    8.4.94 ch 1047/94-0
    22.4.94 ch 1255/94-7
    18.11.1992 CH 3533/92-5
    18.11.1992CH 3533/92-5
    Reply With QuoteRegister To Reply
  2. 04-08-2014, 10:43 PM #2
    sktneer
    sktneer is offline
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,655

    Re: How to split text from text string into separate columns...no delimiters!

    For date part in cell B2 place this array formula. Since this is an array formula so you need to confirm it with Ctrl+Shift+Enter instead of just Enter. (i.e. hold down Ctrl+Shift and then press Enter)

    =LEFT(A2,MIN(IF(ISERROR(SEARCH(CHAR(ROW(INDIRECT("65:90"))),A2)),"",SEARCH(CHAR(ROW(INDIRECT("65:90"))),A2)))-1)
    For remainder part in cell C2 place this array formula. Since this is an array formula so you need to confirm it with Ctrl+Shift+Enter instead of just Enter. (i.e. hold down Ctrl+Shift and then press Enter)
    =MID(A2,MIN(IF(ISERROR(SEARCH(CHAR(ROW(INDIRECT("65:90"))),A2)),"",SEARCH(CHAR(ROW(INDIRECT("65:90"))),A2))),LEN(A2)-MIN(IF(ISERROR(SEARCH(CHAR(ROW(INDIRECT("65:90"))),A2)),"",SEARCH(CHAR(ROW(INDIRECT("65:90"))),A2)))+1)
    For detail see the attached sheet.
    Attached Files Attached Files
    Regards
    sktneer

    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.
    Reply With QuoteRegister To Reply
  3. 04-09-2014, 11:48 AM #3
    droberts2014
    droberts2014 is offline
    Registered User
    Join Date
    04-08-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: How to split text from text string into separate columns...no delimiters!

    Thanks for the reply!
    I am pretty much a newbie at excel functions..somewhat familar with sed/awk and PERL for text processing..
    Can you explain how what you have provided works...and the best methodology for developing my own next time around!
    Thanks!
    Dan
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Split text into array using multiple delimiters
    By TKFRMjarvis in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-26-2014, 03:07 PM
  2. [SOLVED] Split text string into 5 columns
    By Desree86 in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 01-14-2014, 10:07 AM
  3. [SOLVED] How to separate a text string into two columns?
    By pgft in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 06-13-2013, 05:15 AM
  4. Split text string to different columns
    By liarliar in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-04-2008, 09:13 AM
  5. Separate a FirstLast text string into two columns
    By drewannie in forum Excel General
    Replies: 5
    Last Post: 07-05-2006, 07:10 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1