+ Reply to Thread
Results 1 to 12 of 12

Splitting text into columns

  1. 02-02-2023, 06:07 PM #1
    Frick
    Frick is offline
    Forum Contributor
    Join Date
    12-14-2005
    Location
    US
    MS-Off Ver
    Excel 2019, Excel 365, Excel Mobile (Android)
    Posts
    144

    Splitting text into columns

    I have the following text:

    0152A1/2023/10/010040100/840100/2660

    I would like to separate the text above into separate columns based on each forward slash. I have attached a workbook example for reference. What I am needing is VBA script to first separate column A into columns F through K based on the forward slash marks. Then copy the contents of Col's b through D over to Col's L through N. Finally, completing a sum based on the fund code in Col F for each col L, M, N and entering that sum into the reference cells on the dashboard sheet.

    Thank you for any help offered.
    Attached Files Attached Files
    Last edited by Frick; 02-03-2023 at 05:31 PM.
    Reply With QuoteRegister To Reply
  2. 02-02-2023, 06:12 PM #2
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,298

    Re: Splitting text into columns

    Use Text to Columns or formula:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 02-03-2023, 07:01 AM #3
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,298

    Re: Splitting text into columns

    Is this resolved now?
    Reply With QuoteRegister To Reply
  4. 02-03-2023, 07:05 AM #4
    sandy666
    sandy666 is offline
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Splitting text into columns

    option with Power Query

    Column1 Column1.1 Column1.2 Column1.3 Column1.4 Column1.5 Column1.6
    0152A1/2023/10/010040100/840100/2660 0152A1 2023 10 010040100 840100 2660 


    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 02-03-2023, 11:48 AM #5
    Frick
    Frick is offline
    Forum Contributor
    Join Date
    12-14-2005
    Location
    US
    MS-Off Ver
    Excel 2019, Excel 365, Excel Mobile (Android)
    Posts
    144

    Re: Splitting text into columns

    Thank you Sandy. Unfortunately I am not able to use the Power Query on my office computer as it is restricted and admin will not allow. Can this be written in VBA script? Also, I have updated my request and added a worksheet for viewing. Hopefully, you can further assist.
    Reply With QuoteRegister To Reply
  6. 02-03-2023, 12:00 PM #6
    sandy666
    sandy666 is offline
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Splitting text into columns

    VBA should be restricted, it is much more dangerous than PQ
    Reply With QuoteRegister To Reply
  7. 02-03-2023, 12:11 PM #7
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,298

    Re: Splitting text into columns

    And post #2 ????
    Reply With QuoteRegister To Reply
  8. 02-03-2023, 12:31 PM #8
    Frick
    Frick is offline
    Forum Contributor
    Join Date
    12-14-2005
    Location
    US
    MS-Off Ver
    Excel 2019, Excel 365, Excel Mobile (Android)
    Posts
    144

    Re: Splitting text into columns

    Post #2 returned the #NAME error. Also, I further clarified my request by adding a worksheet and being more specific. My error as I should have done so from the onset. Was frustrated as I was/am unable to figure that first part out myself.
    Reply With QuoteRegister To Reply
  9. 02-03-2023, 12:43 PM #9
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,298

    Re: Splitting text into columns

    Old school then, F2:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    dragged across and down.
    Reply With QuoteRegister To Reply
  10. 02-03-2023, 05:30 PM #10
    Frick
    Frick is offline
    Forum Contributor
    Join Date
    12-14-2005
    Location
    US
    MS-Off Ver
    Excel 2019, Excel 365, Excel Mobile (Android)
    Posts
    144

    Re: Splitting text into columns

    Old School works. I would like to use VBA so that I can import the data in Data sheet then run the VBA code to extract the data from column A into Data2 and use a formula to sum the various Fund codes. By using VBA I can make the tables dynamic and that way I can keep adding based on the last row. Using the trim formula I have to copy and paste across columns and rows. I have updated the Example 1 worksheet FYI using the Trim formula provided. However, still in search of the VBA code.
    Reply With QuoteRegister To Reply
  11. 02-03-2023, 08:42 PM #11
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,298

    Re: Splitting text into columns

    VBA solution:

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  12. 02-06-2023, 01:57 PM #12
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,298

    Re: Splitting text into columns

    Thanks for the rep.
    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. Splitting line of text into 3 columns
    By ryu666 in forum Excel General
    Replies: 1
    Last Post: 10-17-2020, 10:08 AM
  2. [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
  3. Splitting text into 3 columns
    By s.b.e in forum Excel General
    Replies: 4
    Last Post: 07-03-2017, 09:37 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

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