+ Reply to Thread
Results 1 to 4 of 4

formula for data to be split to specific columns

  1. #1
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    MS365
    Posts
    1,079

    formula for data to be split to specific columns

    Hi All

    Have a long list of data in a column that need to be split to specific columns headers

    Actually doing this manually, looking for a formula to split packing headers to their respective headers .

    The data varies which depend on the items .

    Expected results in red

    Thanks if anyone can assist.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,047

    Re: formula for data to be split to specific columns

    You can just use Data > Text to Columns. It will take 2 steps. Choose 'Delimited' and then enter * into the 'Other' field. Then the remaining rows can be split by repeating the process and selecting BOTH 'Space' and by entering 'X' into the 'Other' box.

  3. #3
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    MS365
    Posts
    1,079

    Re: formula for data to be split to specific columns

    Quote Originally Posted by hoyasaxa215 View Post
    You can just use Data > Text to Columns. It will take 2 steps. Choose 'Delimited' and then enter * into the 'Other' field. Then the remaining rows can be split by repeating the process and selecting BOTH 'Space' and by entering 'X' into the 'Other' box.
    Thanks for solution but I prefer a formula as the data is dynamic and it shared worksheets

  4. #4
    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
    28,001

    Re: formula for data to be split to specific columns

    Brute force ...

    in C2

    =TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER($A2),"*",REPT(" ",99)),"X",REPT(" ",99)),"BTTLE",""),"CAN",""),"ML",""),"L",""),(COLUMNS($A:A)-1)*99+1,99))

    Copy across and down

+ 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] Excel - Compare data based upon specific criteria split into columns and delimiters
    By Funkadlic01 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 01-01-2017, 09:36 PM
  2. [SOLVED] formula to split data to their respective columns
    By JEAN1972 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-16-2016, 11:50 AM
  3. Split data by delimiters but by formula (not Text to Columns)
    By STUARTXL in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-19-2016, 05:44 AM
  4. Formula to Split Data between two columns
    By sx200n in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-19-2015, 06:50 AM
  5. [SOLVED] Formula required to split uneven data into different columns
    By JEAN1972 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-14-2015, 04:25 AM
  6. [SOLVED] Split specific chars in one column to other columns with no delimiter
    By Fjalar in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-21-2013, 12:28 PM
  7. Text to Columns - Only want to split SPECIFIC NUMBER of times
    By NewKid in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-03-2006, 10:45 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1