macro to move a field

[shelzbelz]

I need to move the contents of a few thousand fields down 1 row and left 1 column. Then go down 1 and right 1 to get the next field to move.

I'm new to macros, but they don't seem to record the arrow keys. And/or I'm mis-using the relative/absolute reference thing.
I can also cut and paste (still extremely tedious) but I can't remember or find how to change a formulat to its result (I will need eventually to delete the cell that the "new" cell refers to.)

help? it's gonna be along night if I can't find a short cut.

[shelzbelz]

Not to complicate things, but I'd also like to separate the street number from the street name which are now in a single field. Is there a shortcut or macro to find the "streetname" and have it and the rest of that cell move to a new cell?

A1 123 Main Street

desired result
A1 123
B1 Main Street

I have a list of all the street names that appear in my worksheet so I can use "find" to locate them, but I don't know the fastest way to complete the process.

[shelzbelz]

Have:
A1 blank B1 Smith John
A2 blank B2 123 Main Street
A3 blank B3 Thomas Amy
A4 blank B4 678 East Avenue

Desired:
A1 blank B1 blank
A2 Smith John B2 123 Main Street
A3 blank B3 blank
A4 Thomas Amy B4 678 East Avenue

And ultimately:
A1 Smith John B1 123 C1 Main Street
A2 Thomas Amy B2 678 C2 East Avenue

[Paul]

Hi shelzbelz,

Sorry, had to revise my post based on your latest info..

Try this:

If your current data is in column B (as shown in your example), starting in B1, then in D1 put the formula:

=OFFSET($B$1,ROW()*2-2,0)

to get the name. In E1 put the formula:

=LEFT(OFFSET($B$2,ROW(B1)*2-2,0),FIND(" ",OFFSET($B$2,ROW(B1)*2-2,0))-1)

to get the house/building number. In F1 put the formula:

=RIGHT(OFFSET($B$2,ROW(B1)*2-2,0),LEN(OFFSET($B$2,ROW(B1)*2-2,0))-LEN(E1)-1)

to get the street info.

Fill that down columns D, E and F as many rows as needed. You can then copy all of those values and use Edit -> PasteSpecial -> Values to put them into Columns A-C.

[shelzbelz]

I don't know what LEN etc mean, but that works and I am deeply grateful. I will be able to apply those formulas to the whole shebang.
Now my first question:

Have:
A1 blank B1 Smith John
A2 blank B2 123 Main Street
A3 blank B3 Thomas Amy
A4 blank B4 678 East Avenue

Desired:
A1 blank B1 blank
A2 Smith John B2 123 Main Street (moved contents of B1 to A2)
A3 blank B3 blank
A4 Thomas Amy B4 678 East Avenue (moved contents of B3 to A4)
Then I will need to delete all the blank rows

[Paul]

Hi Shelz.. please see my revised post. It has new formulas from the previous version (I had not seen your latest post at the time of my first post).

Let me know how those three formulas work.

[shelzbelz]

I bow to your font of wisdom, you solved the whole thing for me.
thank you thank you thank you.

[Paul]

Thanks for the compliment. Easy on the "genius" stuff, though, or others might start thinking I'm useful for something.

I'm glad I could help you out!