Help Writing Macro

L

Luke

I'm looking for help writing a macro for Excel.

I inherited a spreadsheet that I need to work with. In this sheet there is
one column of data that I need to break out. In each cell, there are several
numbers all separated with a comma. (i.e. 200562, 64, 67, 83).

What I'm trying to do is write a macro that will copy the first 6 digits and
paste them to another empty cell in the same row, then go down one row and do
the same thing.

Any suggestions?
 
E

Earl Kiosterud

Luke,


There are some non-macro ways to do this.

Data - Text-to-columns, using a comma as the separator, will separate the
values. You need a few empty columns to the right.

You can also do this with formulas. In another column:

=Left(A2,6)
Copy down with the fill handle.

The remaining data (minus the first comma) could be given by =
=MID(A2, 8,999)
Copied down.

If it isn't always 6 digits

These columns can be converted to permanent values (so the original column
is no longer needed). Select and copy the column, then with it still
selected, Edit - Paste special - values.

If it isn't always 6 digits, a different formula will be needed.
 
N

Niek Otten

Look at the main menu: Data>Text to columns

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
M

Myrna Larson

Why do you want a macro? You can do this with formulas.

If the data is in A1, in the "empty cell in the same row" put the formula

=--LEFT(A1,6)

Copy the formula down. That will return the value as a number, rather than
text.

To get rid of the formulas, use Copy followed by Edit/Paste Special with the
Values option.

If you want to separate all of the numbers, use Data/Text to Columns. See Help
for more information on that if you need it.
 
L

Luke

Thank You!!!

Luke

Myrna Larson said:
Why do you want a macro? You can do this with formulas.

If the data is in A1, in the "empty cell in the same row" put the formula

=--LEFT(A1,6)

Copy the formula down. That will return the value as a number, rather than
text.

To get rid of the formulas, use Copy followed by Edit/Paste Special with the
Values option.

If you want to separate all of the numbers, use Data/Text to Columns. See Help
for more information on that if you need it.
Click to expand...
 
L

Luke

Thanks!!

Luke

Earl Kiosterud said:
Luke,


There are some non-macro ways to do this.

Data - Text-to-columns, using a comma as the separator, will separate the
values. You need a few empty columns to the right.

You can also do this with formulas. In another column:

=Left(A2,6)
Copy down with the fill handle.

The remaining data (minus the first comma) could be given by =
=MID(A2, 8,999)
Copied down.

If it isn't always 6 digits

These columns can be converted to permanent values (so the original column
is no longer needed). Select and copy the column, then with it still
selected, Edit - Paste special - values.

If it isn't always 6 digits, a different formula will be needed.
Click to expand...
 
Top