SEPERATE 1 DOCUMENT CELL DATA TO MULTIPLE CELLS IN ANOTHER EXCEL DOCUMENT

[mfazilm]

Hi,

I actually generate an excel from a system where some cells have multiple data in it and i need to separate those data in 1 cell in 1 document to another documents multiple cells.

Ex:

Excel Document 1(System generated)

NUMBER	LOCATION	NAME
1	NEW YORK	TOM DAVID
2	NEW YORK	PAUL PETER
3	TORONTO	SIMON PERERA
4	MANITOBA	KAMAL MARK

Excel Document 2(Which needs to be prepared) 

NUMBER	LOCATION	FIRST NAME	LAST NAME
1	NEW YORK	TOM	DAVID
2	NEW YORK	PAUL	PETER
3	TORONTO	SIMON	PERERA
4	MANITOBA	KAMAL	MARK

I don't mine a solution with the help of power automate even which could help to reduce my manual work timing. 

[#2]

@mfazilm 

You can use Data > Text to Columns to split the full name into two columns.

It can also be used in VBA.

Power Query is another option.

[#3]

Do you have any guide on how to use Power Query or VBA

[#4]

@mfazilm 

Click in any cell of the data.

On the Data tab of the ribbon, in the Get & Transform Data group, click From Table/Range.

If your data are not in a table yet, you'll be asked to create a table.

The Power Query Editor will open.

Select the NAME column.

On the Home tab of the ribbon, in the Transform group, click Split Column > By Delimiter.

In the Split Column by Delimiter dialog, click OK - no need to change settings.

You'll see the new columns.

Double-click each of the new column headings and change them to FIRST NAME and LAST NAME.

If you want to replace the original data, click Close & Load >Close & Load, otherwise click Close & Load > Close & Load To...

If the latter, you can specify if you want to load the data to the same sheet or to a new sheet, etc.

You'll be able to refresh the query later on.