Extracting data from one column

  • Thread starter Thread starter Craig
  • Start date Start date
C

Craig

I have a column with the following data that I would like
to extract into three columns:

Current data in one column
-------------------------------------
Berlin, Germany - 3,337,000
Riyadh, Saudi Arabia - 3,328,000

Desired result in three columns
-------------------------------------
City Country Population
Berlin Germany 3,337,000
Riyadh Saudi Arabia 3,328,000

Thanks,

Craig
 
Select the column of data
Choose Edit>Replace
In the Find What box, type a comma, and press the spacebar
In the Replace With box, type a character that's not in the
data, e.g. %
Click the Replace All button

In the Find What box, type a hyphen, and press the spacebar
In the Replace With box, leave the % character
Click the Replace All button
Close the Find and Replace dialog box

Choose Data>Text to Columns
Select Delimited, click Next
Check the Other box, and type % in the text box
Click Finish
 
Use Find and Replace to change comma followed by space to dash.
Use Data|Text to Column specifying that the delimiter is dash
 
Back
Top