Results 1 to 3 of 3

Thread: Splitting info out of a cell into other cells.

  1. #1

    Splitting info out of a cell into other cells.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Here is my dilemma: I need to break out specific information from 1 cell and have that info populate in other cells. This is all concerning scheduling my employees and the way the brass upstairs wants to see the info. However it also needs to be presented in a usable fashion for our employees. i.e. Row 1 has the employee name, Mon - Sun, under each corresponding day is the scheduled time and where they are assigned to work. What I need to do then is have the times break out into separate cells ie. start time mon (1 cell) End Time (2nd cell). Take a look at this example.
    original view Mon Tue Wed
    Smith,John 9 a - 5 p Conf 8 a - 4 p Studio 1 12 p - 8 p Maint




    Desired Outcome Mon In Mon Out Tue In Tue Out Wed In Wed Out
    Smith,John 9 a 5 p 8 a 4 p 12 p 8 p

    I am not sure if this is possible. If anyone has any thoughts I would love to hear them. Thanks

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016
    Assuming the data to purge is in B2:Z2, try this...

    In an empty row, where you want the result enter formula in first cell:

    =IF(COLUMNS($B$2:B$2)>COUNTA($B$2:$Z$2)*2,"",TRIM(LEFT(INDEX($B$2:$Z$2,MOD(INT((COLUMN()-COLUMN($B$2))/2),COUNTA($B$2:$Z$2))+1),FIND("-",INDEX($B$2:$Z$2,MOD(INT((COLUMN()-COLUMN($B$2))/2),COUNTA($B$2:$Z$2))+1))-1)))

    then in adjacent cell enter formula:

    =IF(COLUMNS($B$2:B$2)>COUNTA($B$2:$Z$2)*2,"",TRIM(MID(INDEX($B$2:$Z$2,MOD(INT((COLUMN()-COLUMN($B$2))/2),COUNTA($B$2:$Z$2))+1),FIND("-",INDEX($B$2:$Z$2,MOD(INT((COLUMN()-COLUMN($B$2))/2),COUNTA($B$2:$Z$2))+1))+1,5)))

    select both cells and copy across as far as needed.


  3. #3
    This is perfect thank you very much! Taking it one step further, with the current brilliant formula if a cell simply has the word OFF listed it is giving me a #VALUE. Is there an addition that came return the off into the cell?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •