7

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 ?

astropanic
  • 237
  • 1
  • 9

3 Answers3

10

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

Scott Craner
  • 22,693
  • 3
  • 21
  • 25
5

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.

Steven
  • 27,531
  • 11
  • 97
  • 118
3

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

yass
  • 2,534
  • 3
  • 11
  • 18