Search code examples
excelexcel-formulaexcel-2010

Excel we need string data skip Blank and Sort

Right now we're facing some challenge please see the excel below :

enter image description here

enter image description here

we need string the data from those cells and skip blank

Solution

  • you can do a textjoin and a mid. The textjoin allows for empty values to be ignored (2nd argument). If your data is in area B4:J6, then the formula in K4 would be (for "latest" excel):

    =MID(TEXTJOIN("",TRUE,$B4:$J4),COLUMN()-10,1)

    Drag the formula to the right and down (or use fill right (ctrl-R) and fill down (ctrl-D). (replace the column()-10 with what ever column you're using, in my example K is column 10)

    Edit: Sorry, didn't at first notice the excel-2010 tag. This formula should do the same trick:

    =MID(CONCATENATE($B4:$J4),COLUMN()-10,1)