+ Reply to Thread
Results 1 to 6 of 6

Splitting Text Field in to Rows

  1. #1
    Registered User
    Join Date
    03-21-2022
    Location
    Manchester, England
    MS-Off Ver
    Office 365
    Posts
    3

    Splitting Text Field in to Rows

    Hi,

    I use a rota spreadsheet I use to keep track of holidays and sickness.

    One cell has a full list of the days holiday a person has booked, represented as

    20-Jun,21-Jun,22-Jun,23-Jun,24-Jun etc..

    I have a formula which looks at this field and splits the days at the comma and then lists them in individual rows. So i get a list of days instead of all in one cell.
    This works fine if the number of booked days is 33 or below. If it goes to 34 I get a #VALUE error.

    =TRIM(MID(SUBSTITUTE($L$9,",",REPT(" ",999)),(ROW(1:1)-1)*999+1,999))

    L9 is the cell containing the list of days.

    I'm assuming it might be that i've reached the maximum number of data for that cell but not sure how to fix.

    Any help is appreciated!

    thanks.

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,670

    Re: Splitting Text Field in to Rows

    Hi & welcome to the board.
    One option is to change the 999 to 500
    Another option is
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    which will spill down as far as needed.

  3. #3
    Registered User
    Join Date
    03-21-2022
    Location
    Manchester, England
    MS-Off Ver
    Office 365
    Posts
    3

    Re: Splitting Text Field in to Rows

    That's great thank you! Worked perfectly.

    Can I ask what reducing the 999 to 500 achieves? Is it just reducing the loop enough so that the cell limit isn't reached?

    Thanks again!

  4. #4
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,670

    Re: Splitting Text Field in to Rows

    That's right, although using a formula like that, you could end-up with the text string getting spilt in two.
    Which is why I would suggest using the formula I supplied.

  5. #5
    Registered User
    Join Date
    03-21-2022
    Location
    Manchester, England
    MS-Off Ver
    Office 365
    Posts
    3

    Re: Splitting Text Field in to Rows

    Great thanks. I'll give it a go with your version!

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,670

    Re: Splitting Text Field in to Rows

    Glad to help & thanks for the feedback

+ 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] Highlighting every next rows after a row containing specific text and splitting rows
    By Khalid.Noor in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-24-2018, 04:44 AM
  2. splitting text into rows.
    By rose9812in in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 09-14-2015, 09:44 AM
  3. splitting one sheet into several others based on a field, how to get rid of lines
    By Suzanne Hawkins in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-23-2015, 06:30 PM
  4. Splitting a single address field
    By budvegas in forum Excel General
    Replies: 2
    Last Post: 06-16-2015, 08:12 AM
  5. [SOLVED] Need to remove text before the symbols **** in field on many rows
    By jwolsky in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-23-2015, 06:35 PM
  6. Macro splitting comma delimated cell text into rows with other row data copied...
    By geoffffffff in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-13-2013, 11:07 AM
  7. Splitting a field?
    By hyundaiguy in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-07-2008, 04:02 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1