+ Reply to Thread
Results 1 to 2 of 2

Splitting line of text into 3 columns

  1. 10-17-2020, 07:50 AM #1
    ryu666
    ryu666 is offline
    Registered User
    Join Date
    07-13-2018
    Location
    Bangkok, Thailand
    MS-Off Ver
    16.15
    Posts
    2

    Question Splitting line of text into 3 columns

    Hello Excel Geniuses

    I have thousands of lines of text like below
    I need to split it into 3 cells

    example of how I want it
    A1 ก้อนหิมะปั้น
    B1 gaawnF hiL maH bpanF
    C1 snowball

    Each line consists of three sections
    1st section always thai script
    2nd section always multiple words with format xxxxxX
    3rd section english words separated with ; or ,

    ก้อนหิมะปั้น gaawnF hiL maH bpanF snowball
    กางร่ม gaangM rohmF to unfurl an umbrella; open an umbrella
    ก๊าซเรือนกระจก gaasH reuuanM graL johkL greenhouse gases
    การตรวจอากาศ gaanM dtruaatL aaM gaatL weather forecasting
    ตะโก้ dtaL go:hF northwest to southeast monsoon
    แถบอบอุ่น thaaepL ohpL oonL temperate zone
    ท้องฟ้า thaawngH faaH heaven; sky; air; atmosphere; firmament; wind
    น้ำท่วม naamH thuaamF flood; to flood; flooded
    น้ำท่วมฉับพลัน naamH thuaamF chapL phlanM flash floods
    ่นละอองรวม foonL laH aawngM ruaamM total suspended particulate matter
    พายุ phaaM yooH storm
    พายุงวงช้าง phaaM yooH nguaangM chaangH whirlwind, waterspout, tornado

    Any suggestions on how to do this?
    Cheers
    Reply With QuoteRegister To Reply
  2. 10-17-2020, 10:08 AM #2
    mehmetcik
    mehmetcik is offline
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Splitting line of text into 3 columns

    hi this is very easily done.

    Go to the developer tab and select record Macro and record yourself doing this manually. Then you can run the macro to do it automatically for you.

    I assume that you data is in column A. enter this formula in B1 and fill down.

    =SUBSTITUTE(SUBSTITUTE(A1," ","|",1)," ","|",LEN(LEFT(A1,FIND(",",SUBSTITUTE(A1,";",",",1),1)-1))-LEN(SUBSTITUTE(LEFT(A1,FIND(",",SUBSTITUTE(A1,";",",",1),1))," ","")))


    Now you can use the Text to Columns function to split column B using # as a delimiter.

    Stop the Macro recorder.

    Tidy up the recorded macro


    Please Login or Register  to view this content.
    Last edited by mehmetcik; 10-17-2020 at 10:39 AM.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.
    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. [SOLVED] Splitting a particular column of data into seperate Columns (not text to columns)
    By HamerTime7119 in forum Excel General
    Replies: 7
    Last Post: 10-15-2020, 10:38 AM
  2. Splitting text into 3 columns
    By s.b.e in forum Excel General
    Replies: 4
    Last Post: 07-03-2017, 09:37 PM
  3. [SOLVED] Splitting Text String Into Separate Columns
    By robertguy in forum Excel General
    Replies: 2
    Last Post: 02-06-2016, 01:26 PM
  4. [SOLVED] Splitting numbers and text to their respective columns
    By JEAN1972 in forum Excel General
    Replies: 7
    Last Post: 07-31-2014, 06:48 AM
  5. splitting text string into different columns
    By lbm1978 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-13-2013, 05:13 PM
  6. Splitting Text Across Columns
    By soma2004 in forum Excel General
    Replies: 1
    Last Post: 07-27-2007, 06:38 PM
  7. [SOLVED] Splitting text to columns
    By nospaminlich in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-06-2006, 05:30 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1