Extracting text to from one cell to create columns and rows

[akin.seweje]

Is anybody out there who can help me.
I have a worksheet with one column and it contains text of the format
[[firstname] [lastname] [company name]] all in one cell thereby
creating a record (see A below).

The next row contains entry for a different record with the same format
and have rows and rows of records in the same format described above.

What I will like to do is to extract in each cell the name text and put
it in a different column (e.g. column A) and the company name text in
another column (e.g. column B) on row 1. Then go to the next cell and
repeat the same for the next record - row 2 and so on....

A. Current Excel worksheet structure (sheet 1)
=========================
A B C
1 firstname lastname
companyname

2 firstname lastname
companyname

3 firstname lastname
companyname

4

B. Proposed Excel worksheet structure (sheet 2 - what I will like to
achieve)
=========================
A B C
1 firstname lastname companyname
2 firstname lastname companyname
3 firstname lastname companyname

Is there a way to automate by writing a macro achieve and turn the
format in A above into B for all records when run?

 

[PY & Associates]

First use text to column, separate by space;
then use "&" to join firstname and lastname

Cheers