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 ?

• Will each cell of this type have exactly three comma delimited numbers? May 16, 2017 at 17:45
• yes, all single digit from 1 to 8 May 16, 2017 at 18:05
• You could also try the "text to column" function in Excel May 17, 2017 at 8:01

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

Where the `2` is the index you want. 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. • What's the reason behind using `REPT`? and `999` here?
– Rob
May 17, 2017 at 7:22
• It is giving me a large target to hit. 999 spaces that the trim then removes. May 17, 2017 at 12:25

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))
``````

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