+ Reply to Thread
Results 1 to 16 of 16

How to put different values from one cell into multiple rows

  1. 02-22-2024, 01:38 PM #1
    Ninajigar
    Ninajigar is offline
    Registered User
    Join Date
    03-04-2019
    Location
    USA
    MS-Off Ver
    Microsoft 365 - V2308
    Posts
    33

    How to put different values from one cell into multiple rows

    Hi,
    I have a number of cells that each include multiple names. I need to have each of these names to be in a separate row, is it possible?


    Thanks,
    Nina
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 02-22-2024, 01:54 PM #2
    TMS
    TMS is offline
    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,447

    Re: How to put different values from one cell into multiple rows

    This:
    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-22-2024, 02:03 PM #3
    Ninajigar
    Ninajigar is offline
    Registered User
    Join Date
    03-04-2019
    Location
    USA
    MS-Off Ver
    Microsoft 365 - V2308
    Posts
    33

    Re: How to put different values from one cell into multiple rows

    It doesn't work - I get this error: #NAME?
    Reply With QuoteRegister To Reply
  4. 02-22-2024, 02:10 PM #4
    DJunqueira
    DJunqueira is offline
    Forum Expert
    Join Date
    10-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365 V2401 w/ Win10 Home 64 Bit
    Posts
    2,014

    Re: How to put different values from one cell into multiple rows

    Use this instead.

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 02-22-2024, 02:14 PM #5
    Fluff13
    Fluff13 is online now
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,080

    Re: How to put different values from one cell into multiple rows

    How about
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 02-22-2024, 03:35 PM #6
    TMS
    TMS is offline
    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,447

    Re: How to put different values from one cell into multiple rows

    @Ninajigar: It doesn't work - I get this error: #NAME? That probably means you are not using Excel 365.

    @DJ: what is this: =TEXTSPLIT(LC[-5],,CHAR(10)) ???
    Reply With QuoteRegister To Reply
  7. 02-22-2024, 03:38 PM #7
    Fluff13
    Fluff13 is online now
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,080

    Re: How to put different values from one cell into multiple rows

    I suspect that should be RC[-5] using R1C1 notation, but in Brazilian.
    Reply With QuoteRegister To Reply
  8. 02-22-2024, 03:51 PM #8
    TMS
    TMS is offline
    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,447

    Re: How to put different values from one cell into multiple rows

    Whatever, doesn’t work for me. And I’ve never used RC notation in a worksheet.
    Reply With QuoteRegister To Reply
  9. 02-22-2024, 03:53 PM #9
    DJunqueira
    DJunqueira is offline
    Forum Expert
    Join Date
    10-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365 V2401 w/ Win10 Home 64 Bit
    Posts
    2,014

    Re: How to put different values from one cell into multiple rows

    Not Brazilian, I just got the same Error and when I point to A2 I notice that the sheet was setup to R1C1.
    Reply With QuoteRegister To Reply
  10. 02-22-2024, 04:43 PM #10
    Ninajigar
    Ninajigar is offline
    Registered User
    Join Date
    03-04-2019
    Location
    USA
    MS-Off Ver
    Microsoft 365 - V2308
    Posts
    33

    Re: How to put different values from one cell into multiple rows

    Thanks for your help. But it doesn't work if you have more than one cell. Please see the attached. I need the values in each cell get broken down for each line. I copy pasted A2 cell, but A3, A4,.... each includes different names.
    Attached Images Attached Images
    Last edited by Ninajigar; 02-22-2024 at 05:15 PM.
    Reply With QuoteRegister To Reply
  11. 02-22-2024, 04:50 PM #11
    Fluff13
    Fluff13 is online now
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,080

    Re: How to put different values from one cell into multiple rows

    Which formula are you using?
    Reply With QuoteRegister To Reply
  12. 02-22-2024, 05:08 PM #12
    Ninajigar
    Ninajigar is offline
    Registered User
    Join Date
    03-04-2019
    Location
    USA
    MS-Off Ver
    Microsoft 365 - V2308
    Posts
    33

    Re: How to put different values from one cell into multiple rows

    =FILTERXML("<k><m>"&SUBSTITUTE(RC[-4],CHAR(10),"</m><m>")&"</m></k>","//m")
    Reply With QuoteRegister To Reply
  13. 02-22-2024, 05:09 PM #13
    Ninajigar
    Ninajigar is offline
    Registered User
    Join Date
    03-04-2019
    Location
    USA
    MS-Off Ver
    Microsoft 365 - V2308
    Posts
    33

    Re: How to put different values from one cell into multiple rows

    Using this:
    =FILTERXML("<k><m>"&SUBSTITUTE(RC[-4],CHAR(10),"</m><m>")&"</m></k>","//m")
    Reply With QuoteRegister To Reply
  14. 02-22-2024, 05:45 PM #14
    Fluff13
    Fluff13 is online now
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,080

    Re: How to put different values from one cell into multiple rows

    If you use R1C1 references it should be
    =FILTERXML("<k><m>"&SUBSTITUTE(RC1,CHAR(10),"</m><m>")&"</m></k>","//m")
    Reply With QuoteRegister To Reply
  15. 02-23-2024, 01:10 AM #15
    Ninajigar
    Ninajigar is offline
    Registered User
    Join Date
    03-04-2019
    Location
    USA
    MS-Off Ver
    Microsoft 365 - V2308
    Posts
    33

    Re: How to put different values from one cell into multiple rows

    Could you explain the code?
    what is RC1referring to?
    what do k and M refer to?


    I truly appreciate your time.
    Reply With QuoteRegister To Reply
  16. 02-23-2024, 08:30 AM #16
    Fluff13
    Fluff13 is online now
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,080

    Re: How to put different values from one cell into multiple rows

    Did the formula work?
    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] Multiple Rows of same id to bring in one cell along with all values
    By shah.ghazanfar in forum Excel General
    Replies: 7
    Last Post: 03-16-2023, 11:44 AM
  2. [SOLVED] Insert Multiple rows based on the values in the cell
    By rlamba in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-13-2014, 09:27 AM
  3. [SOLVED] Create multiple rows from cell value whilst preserving other cell values
    By Kjellis85 in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 11-15-2013, 03:47 AM
  4. [SOLVED] Separating values in a cell into multiple rows
    By mfeinf in forum Excel General
    Replies: 7
    Last Post: 08-13-2013, 09:21 AM
  5. Split multiple values in a cell to multiple rows
    By karmithr in forum Excel General
    Replies: 2
    Last Post: 03-04-2013, 08:34 PM
  6. vLookupAll output to multiple rows instead of multiple values in cell?
    By s_t_e_i_n in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 10-03-2012, 09:13 AM
  7. copy rows based on multiple cell values
    By stevenwhite1968 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-21-2011, 07:18 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