How to Separate Text and Numbers Using Formulas?

Question

I need help with an Excel formula to extract text and numbers into separate columns. I'm using Excel 2016 and have data in cells A1 to A4, as follows:

A1: abc
A2: 123
A3: def
A4: 456
A5: ghi
A6: 789
A7: jkl

I want to get the text values in one column (e.g., B1 to B4) and the number values in another column (e.g., C1 to C4). How can I achieve this?

Any guidance would be greatly appreciated!

Thanks!

Answer 1

Here are few alternative ways one could try using Excel Formulas in Excel 2016:

---

• Method One:

=IFERROR(INDEX($A$1:$A$7,COLUMNS($A$1:A1)+(ROWS(A$1:A1)-1)*2),"")

---

The above formula needs to fill down and fill right also assumes that the pattern will be consistent and every alternative rows has text and numbers, therefore the above should work, however, if thats not the scenario here then, try the one mentioned below:

• Method 2:

For Texts:

=IFERROR(INDEX($A$1:$A$7,
 AGGREGATE(15,7,ROW($A$1:$A$7)/
 ISTEXT($A$1:$A$7),ROWS(E$1:E1))),"")

---

For Numbers:

=IFERROR(INDEX($A$1:$A$7,
 AGGREGATE(15,7,ROW($A$1:$A$7)/
 ISNUMBER($A$1:$A$7),ROWS(F$1:F1))),"")

---

_{NOTE: Line breaks and spacing are for display only. Remove either, both, or none at your discretion.}