+ Reply to Thread
Results 1 to 9 of 9

formula for multiple split headers of text and numbers to respective headers

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

    formula for multiple split headers of text and numbers to respective headers

    I have an extract of data list which has columns of multiple numbers and text which are separated by a comma .

    my goal to split header of columns A , to their respective headers which has multiple numbers and text.

    Some have one description , others have multiple code numbers and description which need to be split .

    All numbers consists of 8 digits only , but description varies in length eg powder (aaaa s113)
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    03-14-2013
    Location
    England
    MS-Off Ver
    2007
    Posts
    186

    Re: formula for multiple split headers of text and numbers to respective headers

    Hi,

    I was just having a go at this, not sure it's the best way but it's one way you could potentially do it....

    In Cell B2 - copy down....

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    In cell C2 - copy down...

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    In cell D2 - copy down...

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    In cell E2 - copy down...

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I didn't go any further as it kind of depends how many columns this could go up to if it would be worth it, using this method the formula is different for each column, I don't know how to write one that would work for all columns. What is the max possible number of commas in one of the cells in column A?

    I suspect someone else will come up with a better way to do this but there's my input so far.

  3. #3
    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,005

    Re: formula for multiple split headers of text and numbers to respective headers

    In N2

    =TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",99)),(COLUMNS($A:A)-1)*99+1,99))

    Copy across (for as many columns as maximum headers) and down

    in B2

    =IFERROR(LEFT(N2,8),"")

    Copy down

    in C2

    =IFERROR(TRIM(RIGHT(N2,LEN(N2)-8)),"")

    Repeat B2 & C2 in D2 & E2 changing N to O.... etc

    See attached
    Attached Files Attached Files

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: formula for multiple split headers of text and numbers to respective headers

    I copied column A from Sheet1 to a new Sheet2 and put the formulae in that sheet, with a helper column in column B. The formulae generally use LEFT and SUBSTITUTE functions - let me know if you need explanations.

    Hope this helps.

    Pete
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: formula for multiple split headers of text and numbers to respective headers

    Or try this ...

    In B2:

    =LEFT(TRIM(MID(SUBSTITUTE(","&$A2,",",REPT(" ",200)),INT(COLUMNS($A:B)/2)*200,200)),8)

    Copy to D2, F2, H2, ...

    In C2:

    =MID(TRIM(MID(SUBSTITUTE(","&$A2,",",REPT(" ",200)),INT(COLUMNS($A:C)/2)*200,200)),9,200)

    Copy to E2, G2, I2, ...

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

    Re: formula for multiple split headers of text and numbers to respective headers

    Hi John

    Trying your techniques but an issue has arisen when splitting in 4 th attempt, the split does not correspond to what i was expecting column Q R and S.

    BUTwhen chaging to TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",99)),(COLUMNS($A:A)-1)*99+15,99))it works but I have no clue why but repeating this item , it got correctly in another column???

    Could you look in column Q R AND S in green and advise
    Attached Files Attached Files

  7. #7
    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,005

    Re: formula for multiple split headers of text and numbers to respective headers

    You have more than 6 headers so insert more columns after column M
    Attached Files Attached Files

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

    Re: formula for multiple split headers of text and numbers to respective headers

    Thanks John

  9. #9
    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,005

    Re: formula for multiple split headers of text and numbers to respective headers

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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] Need Macro to help split data into multiple tabs, while keeping headers, formatting, etc
    By Centara in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-15-2016, 04:37 PM
  2. autofilter method of range class failed -header has tables headers and non table headers
    By naveen.acheanz in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-16-2016, 08:21 AM
  3. Replies: 1
    Last Post: 02-03-2016, 02:33 AM
  4. Replies: 2
    Last Post: 09-01-2013, 07:36 PM
  5. Vertical text (row headers) being split on printed pages
    By DARS CPCSC in forum Excel General
    Replies: 4
    Last Post: 12-07-2012, 02:18 PM
  6. Macro for comparing headers and copy/pasting respective columns
    By Sarushka in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-04-2009, 08:48 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