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

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


4,8,2

Formulas for each number.

=LEFT(A1, SEARCH(",",A1,1)-1)
=MID(A1, SEARCH(",",A1) + 1, SEARCH(",",A1,SEARCH(",",A1)+1) - SEARCH(",",A1) - 1)
=RIGHT(A1,LEN(A1) - SEARCH(",", A1, SEARCH(",", A1) + 1))

Source: https://www.ablebits.com/office-addins-blog/2016/06/01/split-text-string-excel/

Note: This will only work for three comma delimited numbers.


You can use Mid in Excel, the following formula will find the first "," and second "," to extract the value between it:
=MID(A1,FIND(",",A1,1)+1,FIND(",",SUBSTITUTE(A1,","," ",1),1)-1-FIND(",",A1,1))
whatever the value is one or more digits