+ Reply to Thread
Results 1 to 8 of 8

Convert Two line record in single line from text to excel

  1. 04-20-2025, 09:58 AM #1
    kv.singh
    kv.singh is offline
    Forum Contributor
    Join Date
    10-30-2019
    Location
    GUJARAT,INDIA
    MS-Off Ver
    MS OFFICE 2016
    Posts
    233

    Convert Two line record in single line from text to excel

    each record is separated by line ------------------
    each record is spread in two lines.
    First 65 lines in "input" sheet is blank (no data)
    I want to create code which will convert text file in excel with each record (which is in two lines in text file) single line in excel.
    input text file and expected output file is attached.
    please help
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 04-20-2025, 11:21 AM #2
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    30,915

    Re: Convert Two line record in single line from text to excel

    Very diffucult (if possible at all) as there is no separator between fields:.using blanks as the separator is not possible

    e.g. RQJUQHQI Q MQRMQR is split as RQJUQHQI Q / MQRMQR whereas
    Q Q SQIYQM is treated as a single field

    and this.......

    QQrot MQrshQnQQen >>>QQrot MQrs / hQnQQen
    Attached Images Attached Images
    Last edited by JohnTopley; 04-20-2025 at 11:32 AM.
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
    Reply With QuoteRegister To Reply
  3. 04-20-2025, 12:24 PM #3
    kv.singh
    kv.singh is offline
    Forum Contributor
    Join Date
    10-30-2019
    Location
    GUJARAT,INDIA
    MS-Off Ver
    MS OFFICE 2016
    Posts
    233

    Re: Convert Two line record in single line from text to excel

    can we extract record between ---------- in two cells line1 in cell A and line 2 in cell 2 (refer attached file)
    without header Q O C No. Q O C Mt. MeM. Qmt. MeM.TyMe. MroM. No. Molicy No. QMj. Mt. QMj. Qmt. O/S Qmt.
    NQme . Qgency coMe. Mev-Off/CliQ MlQn
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 04-20-2025, 01:28 PM #4
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2505 Win 11
    Posts
    24,754

    Re: Convert Two line record in single line from text to excel

    Using Power Query 

    Please Login or Register  to view this content.
    to apply the function "Space Trim" to remove the extra spaces

    Please Login or Register  to view this content.
    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.

    - Follow this link for an introduction to Power Query functionality.

    - Follow this link for a video which demonstrates how to use Power Query code provided.
    Attached Files Attached Files
    Last edited by alansidman; 04-21-2025 at 04:04 AM.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
    Reply With QuoteRegister To Reply
  5. 04-20-2025, 02:04 PM #5
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    30,915

    Re: Convert Two line record in single line from text to excel 

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 04-20-2025, 06:45 PM #6
    mjr veverka
    mjr veverka is offline
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,278

    Re: Convert Two line record in single line from text to excel

    From text file ... of course, if properly prepared:
    ... Happy Easter ...
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  7. 04-21-2025, 03:39 AM #7
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    30,915

    Re: Convert Two line record in single line from text to excel 

    Please Login or Register  to view this content.
    Last edited by JohnTopley; 04-21-2025 at 03:43 AM.
    Reply With QuoteRegister To Reply
  8. 04-21-2025, 12:06 PM #8
    kv.singh
    kv.singh is offline
    Forum Contributor
    Join Date
    10-30-2019
    Location
    GUJARAT,INDIA
    MS-Off Ver
    MS OFFICE 2016
    Posts
    233

    Re: Convert Two line record in single line from text to excel

    Thanks @ mjr veverka
    Thanks @ John for your continous support
    Thanks @ alansidman
    All codes and power query solution are excellent and giving desired result.
    Proud to be part of this Forum.
    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] convert text file to excel and extract each record in single line
    By kv.singh in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-19-2025, 05:43 PM
  2. [SOLVED] Display only the first line of multi-line and/or single line cell?
    By stefan.g in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-09-2025, 01:43 AM
  3. Excel macro of copy paste that transale both line and column into a single line
    By aubjon82 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-14-2024, 12:53 AM
  4. Extract a specific line from a excel cell (containing multi line text)
    By anonymous321 in forum Excel General
    Replies: 14
    Last Post: 12-31-2023, 01:53 AM
  5. Creating single line record from a Pivot table or even the original data
    By Excel_Al in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 07-05-2017, 07:34 AM
  6. Convert multi-line records to single line?
    By automator in forum Excel - New Users/Basics
    Replies: 10
    Last Post: 06-07-2010, 09:46 AM
  7. Read the text in a single cell line by line.
    By moon in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-14-2010, 06:23 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1