Convert text to column using macro

  • Thread starter Thread starter LSB
  • Start date Start date
L

LSB

Hi, would like to find out how can i write a macro to
convert a list of text in column.
For example:
3A 0.320 0.320 0.310 0.315 -0.005
A&M 0.900 0.880 0.880 0.880 -0.020
A&M-WA 0.420 N/A N/A N/A N/A

I copy this data into excel sheet in column A, and by
running the macro I want to convert the data into
individual column.

A B C D E F
----------------------------------------------------
3A 0.320 0.320 0.310 0.315 -0.005
A&M 0.900 0.880 0.880 0.880 -0.020
A&M-WA 0.420 N/A N/A N/A N/A


Thanks in advance.
 
You have a couple of options here.

If you are copying this information from an existing text
file, you might want to consider writing an Import macro.
Start a macro recording. Click File/Open, under the Files
of type list, check All Files then find your text file.
This will automatically launch the Text Import Wizard.
Follow its instructions on parsing the file into the
correct columns. Copy the data over to your workbook,
then stop the macro recording.

If you are copying this information directly from some
other program, then you can use the Text to Columns
wizard. Copy the data to column A. Start the macro
recorder. Select column A (the whole column needs to be
selected, not just one cell). From the Data menu select
Text to Columns. From this point it's identical to the
above Text Import Wizard. Just follow the wizards
instructions. Stop the macro recorder.
 
Back
Top