Extracting string separately from Excel

Question

I have rows in Excel in which there is

[test].[test1]

How can I extract test and test1 separately ?

Answer 1

The easiest way is to use text to columns (as mentioned by pnuts) but here's a couple of other options to explore:

---

VBA:

 x = Split(Range("A1"), ".")
  Debug.Print x(0) '// Prints "test"
  Debug.Print x(1) '// Prints "test1"

---

Formula:
=LEFT(A1,SEARCH(".",A1)-1) Will return "test"
=MID(A1,SEARCH(".",A1)+1,LEN(A1)) Will return "test1"

---

Answer 2

Select the range of cells in a column on which you want to perform the split action. Click 'Data'. From 'Data Tools', Click 'Convert Text to Table'. Click 'Next'. Select the appropriate delimiter check box. Click 'Next'. From 'Column data format', select appropriate option. Click 'Finish'.

Answer 3

Assuming the the string is in A1, then paste this formula to B1:

=TRIM(MID(REPT(" ", 999)&SUBSTITUTE(SUBSTITUTE($A1, "[", REPT(" ", 999)), "]", REPT(" ", 999)), COLUMN(A:A)*1999, 999))

You can copy the formula in B1 to C1, D1, E1,... to get 2nd, 3rd,... word.