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 ?

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

3 Answers 3

9

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

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.