|
Relative Macro Help on Keystrokes
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. Befo 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: Befo cell 1 cell 2 aaaa bbbb Desired After: cell 1 cell 2 aaaa bbbb this one works. next set of two cells, befo 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. -- Neal Z |
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 Zimm wrote: 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. Befo 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: Befo cell 1 cell 2 aaaa bbbb Desired After: cell 1 cell 2 aaaa bbbb this one works. next set of two cells, befo 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. -- Neal Z -- Dave Peterson |
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. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Neal Zimm" wrote in message ... 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. Befo 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: Befo cell 1 cell 2 aaaa bbbb Desired After: cell 1 cell 2 aaaa bbbb this one works. next set of two cells, befo 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. -- Neal Z |
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)) -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Earl Kiosterud" wrote in message ... 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. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Neal Zimm" wrote in message ... 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. Befo 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: Befo cell 1 cell 2 aaaa bbbb Desired After: cell 1 cell 2 aaaa bbbb this one works. next set of two cells, befo 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. -- Neal Z |
thanks. give me a day or two to try this out.
Neal "Earl Kiosterud" wrote: 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)) -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Earl Kiosterud" wrote in message ... 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. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Neal Zimm" wrote in message ... 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. Befo 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: Befo cell 1 cell 2 aaaa bbbb Desired After: cell 1 cell 2 aaaa bbbb this one works. next set of two cells, befo 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. -- Neal Z |
Oh yes, one more thing, the original cell contents from left to right a
a 4 digit number, a blank, an alphanumeric character string varying from all blanks to a string of 20 characters. "Earl Kiosterud" wrote: 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)) -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Earl Kiosterud" wrote in message ... 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. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Neal Zimm" wrote in message ... 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. Befo 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: Befo cell 1 cell 2 aaaa bbbb Desired After: cell 1 cell 2 aaaa bbbb this one works. next set of two cells, befo 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. -- Neal Z |
Thanks. give me a day or two to try it out.
Neal "Dave Peterson" wrote: 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 Zimm wrote: 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. Befo 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: Befo cell 1 cell 2 aaaa bbbb Desired After: cell 1 cell 2 aaaa bbbb this one works. next set of two cells, befo 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. -- Neal Z -- Dave Peterson |
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 Zimm wrote: Oh yes, one more thing, the original cell contents from left to right a a 4 digit number, a blank, an alphanumeric character string varying from all blanks to a string of 20 characters. "Earl Kiosterud" wrote: 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)) -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Earl Kiosterud" wrote in message ... 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. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Neal Zimm" wrote in message ... 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. Befo 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: Befo cell 1 cell 2 aaaa bbbb Desired After: cell 1 cell 2 aaaa bbbb this one works. next set of two cells, befo 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. -- Neal Z -- Dave Peterson |
functions worked nicely. thanks.
Neal "Dave Peterson" wrote: 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 Zimm wrote: Oh yes, one more thing, the original cell contents from left to right a a 4 digit number, a blank, an alphanumeric character string varying from all blanks to a string of 20 characters. "Earl Kiosterud" wrote: 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)) -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Earl Kiosterud" wrote in message ... 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. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Neal Zimm" wrote in message ... 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. Befo 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: Befo cell 1 cell 2 aaaa bbbb Desired After: cell 1 cell 2 aaaa bbbb this one works. next set of two cells, befo 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. -- Neal Z -- Dave Peterson |
Functions worked nicely, thanks so much.
Neal "Earl Kiosterud" wrote: 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)) -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Earl Kiosterud" wrote in message ... 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. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Neal Zimm" wrote in message ... 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. Befo 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: Befo cell 1 cell 2 aaaa bbbb Desired After: cell 1 cell 2 aaaa bbbb this one works. next set of two cells, befo 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. -- Neal Z |
| All times are GMT +1. The time now is 03:26 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com