Relative Macro Help on Keystrokes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need a work around to the following situation.
I'm editing a lot of cells to split the data into two cells and there is a
pattern, but Excel is picking up 'keystrokes' I don't want. aaaa is always a
4 digit number, and bbbb is various text.
Before:
cell 1 cell 2
aaaa bbbb

Desired After:
cell 1 cell 2
aaaa bbbb

I recorded a relative macro, and I get the desired result the first time,
BUT, not the second.
Example:
Before:
cell 1 cell 2
aaaa bbbb

Desired After:
cell 1 cell 2
aaaa bbbb this one works.

next set of two cells, before:
cell 1 cell 2
cccc dddd

After Actual when macro is run:
cell 1 cell 2
aaaa bbbb

even tho' the macro is relative, Excel is obviously remembering the value's
aaaa and bbbb on subsequent cells; this I do NOT want.

Help? Thanks.
 
Try recording a macro when you select the range, and do data|Text to columns.

You can specify that the column is delimited by space.

If you have trouble generalizing your code, post back with what you have.
 
Neal,

Excel's macro language is not a keystroke language. The macro recorder will
record whatever data you leave the cell with -- it doesn't care how you did
it.

For your situation, if you want it split in half, you could put a formula in
col's B anc C:
=LEFT(A2, 4) =RIGHT(A2, 4)

To convert the formulas to actual data, copy columns B and C, then do Paste
Special - Values (right over them). now you don't need the original stuff
in Column A any more.

If you splilt the data on something other than 4 characters, tell us what
the criteria is, and we'll go from there.
 
Neal,

I just noticed that there's a space between aaaa and bbbb. I'll submit the
following formulas instead:

=LEFT(A2, SEARCH(" ", A2)-1)
=RIGHT(A2, LEN(A2)-SEARCH(" ",A2))
 
Oh yes, one more thing, the original cell contents from left to right are:
a 4 digit number,
a blank,
an alphanumeric character string varying from all blanks to a string of 20
characters.
 
If it's always that format and you can use a couple of formulas:

=left(a1,4)
(4 leftmost characters)

=mid(a1,6,255)
(starts at character #6 and goes far enough to the right to get past your 20
character limit.)

Neal said:
Oh yes, one more thing, the original cell contents from left to right are:
a 4 digit number,
a blank,
an alphanumeric character string varying from all blanks to a string of 20
characters.
Click to expand...
 
functions worked nicely. thanks.
Neal


Dave Peterson said:
If it's always that format and you can use a couple of formulas:

=left(a1,4)
(4 leftmost characters)

=mid(a1,6,255)
(starts at character #6 and goes far enough to the right to get past your 20
character limit.)
Click to expand...
 
Functions worked nicely, thanks so much.
Neal

Earl Kiosterud said:
Neal,

I just noticed that there's a space between aaaa and bbbb. I'll submit the
following formulas instead:

=LEFT(A2, SEARCH(" ", A2)-1)
=RIGHT(A2, LEN(A2)-SEARCH(" ",A2))
Click to expand...
 
Back
Top