Excel – Spreadsheet (Excel, Google Docs etc), extracting value separated by comma

google-spreadsheetsmicrosoft excelspreadsheet

I have a value in one cell that looks like this

4,8,2

Lets say it's in the A1 cell

how I can get the inner 8 (the second comma separated value) from this cell into the cell B1 ?

=?

Tried with =SPLIT(A1, ","), but this splits the values and writes them to multiple cells.

Any ideas ?

Best Answer

In Google Sheets:

=INDEX(SPLIT(A1,","),2)

Where the 2 is the index you want.

enter image description here

In Excel:

=TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",999)),(2-1)*999+1,999))

Or this array formula:

=INDEX(TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",999)),(ROW(INDIRECT("1:" & LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))-1)*999+1,999)),2)

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter Instead of Enter when exiting edit mode. If done correctly then excel will put {} around the formula.

Again replace the 2 with the desired index.

![enter image description here

Related Question