0

assume in an excel file, cell a1 contains (comma separated values) 100, 250, 3. Then how can one write a formula for cells a2, a3, and a4 such that cell a2 becomes 100, a3 becomes 250, and a4 becomes 3.

Meysam Sadeghi
  • 1,483
  • 2
  • 17
  • 23
  • 1
    The linked duplicate is about how to split data on a specific delimiter. There are several good answers that you can draw ideas from. Personally I like to use [`FILTERXML()`](https://stackoverflow.com/q/61837696/9758194) which is also included in the linked dupe. Just transpose the data. Just know that another viable option would be to use PowerQuery which allows to split data into rows too. – JvdV Mar 09 '22 at 09:41

1 Answers1

3

enter image description here

I'm pretty sure there is an easier way to do this, specially if you got E365, but you can make a work around to handle it.

Formula in column B is: =VALUE(LEFT(A1;SEARCH(",";A1)-1))

Column C: =VALUE(LEFT(SUBSTITUTE(A1;B1&", ";"");SEARCH(",";SUBSTITUTE(A1;B1&", ";""))-1))

Column D: =VALOR(SUBSTITUTE(A1;B1&", "&C1&", ";""))

Notice this will work only if your data is always like the pattern you posted:

INTEGER, INTEGER, INTEGER

If the pattern changes, the formula won't work!

Another aproach woould be using an UDF in VBA:

Public Function RETURN_INDEX(ByVal vRng As Range, ByVal vIndex As Integer) As Integer
RETURN_INDEX = Split(vRng.Text, ", ")(vIndex - 1)
End Function

This UDF will ask for the rng containing your data and what index you want (first one, second one, and so no)

So, to get 250 (second one) you would do: =RETURN_INDEX(A1;2)

Foxfire And Burns And Burns
  • 10,665
  • 2
  • 17
  • 38