Help Macro

[Gary]

Hi,
Help! Need help creating a macro for Excel 97 (I also have Excel 2000)
I have a large (aprox 100k rows) Excel spreadsheet made up of columns named
" names, street, city, state, zip, phone and company,"
In many of the cell the information is split between cells. Example
"Brown, James" is split between two cells with "Brown" and "James" in two
separate cells.
I need a macro that will cut "James" out of the cell on the right and add to
the cell to the left, then delete the cell "James" was in, moving the
remaining cells to the left.
Also Another problem is the opposite. Example: "James Brown 444 Main St."
is all in the same cell (instead of "James Brown" and "444 Main St." and I
need to cut the address out and put it into an inserted new cell directly to
the right of the original cell.


I can do these things manually but not with the Excel macro recorder.

I couldn't even make a macro that would cut the info in one cell and move
it the left and delete the now empty cell.

I have been able to do the macros with MS old universal macro recorder I
found on a old machine. The problem is it uses up so much memory that after
using the
macro just a 30 or 40 times the machine runs out of Ram (I have 256k on a
Win2000 machine) and the machine starts to slow and lock up and needs to be
restarted.


Thanks for any help,

Gary

(e-mail address removed)

 

[Dave Peterson]

You've already seen that splitting up cells into component parts looks
difficult--so don't make your future work more difficult.

Keep the first name and last name in separate fields (columns). You can always
merge them later when you need them with a simple formula:

=A1&B1
or
=A1& " " & B1
(or some variation)

You can always hide these columns if you find them irritating.

The second portion is a lot more labor intensive.

If you have
"James Brown 444 Main Street" in one cell, but
"James Brown, Jr 123 North 2nd Street", and
"James Earl Brown, III 345 East Gate Park Avenue" in a third

it becomes a problem very quickly.

I think I'd do this:
Insert a few columns to the right of this column (so as not to overwrite
existing data)

Split it up (data|text to columns, delimited (spaces)) and plop each piece into
its own column.

Then use another helper column to find out how many pieces you have.

=counta(L1:S1)
and drag down

Sort by this column and try to find pattern matches.

If you're lucky, each piece with 5 pieces will be:
first, last, number, street1, street2.

You can use a formula like the one you used before =a1& " " & b1 to concatenate
the strings together--but I'd still keep the first and last name separate.

Then it becomes an eyeball problem. You find patterns and build formulas.

Remember to save often (and save as new names so in case you screw up, you can
go back to a good one.)