Extracting numbers from cell in Excel-CodePudding

I want to extract X and Y from 2021-08-26 06:10:03,1,"",X,Y, which is in a cell in excel. X and Y can be either one, two, or three digit numbers. I want to extract X and Y from thousands of rows with the date and time different on each row. How can I do this?

CodePudding user response：

If you have Excel 365 current channel you can use this formula (where your data are in column A):

=CHOOSECOLS(TEXTSPLIT(A1:A100,","),4,5)

It first splits your data into single parts/cells based on the comma. Then it takes the 4th and 5th part (which could be one, two or three digit numbers - that's for this approach irrelevant).

UPDATE: Without Textsplit

=FILTERXML("<t><s>" &SUBSTITUTE(A1,",","</s><s>") & "</s></t>", "//s[position()=4 or position()=5]")

Filterxml builds a XML-string from which we take the 4th and 5th tag.

To return two columns (instead of two rows):

=TRANSPOSE(FILTERXML("<t><s>" &SUBSTITUTE(A1,",","</s><s>") & "</s></t>", "//s[position()=4 or position()=5]"))