Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
move part of cell to new column
I would like to know if there is some code that can help me with the
following. I have a column of cells with information similar to this: $50.00 Gift from Johnson, John $35.00 Gift from Public, Jimmy $75.00 Gift from Jones, Billy etc. The second part (Gift...) is not necessarily lined up vertically. I would like to move the "Gift from Johnson, John" part to a new column. Thanks for your help, Scott |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
move part of cell to new column
Hi SE
if you want to take "gift from ..." out of the original column and put it in its own column then one method is - insert two new columns to the right of your current column -select the currnt column -choose data / text to columns -choose fixed width, Next -in the little preview window click directly after the .00 in the dollar amount and before the word "gift" -click finish this should give you the dollar amounts in one column, a blank column & then the "gift .... " in the next column now click on the first line of the blank column (in the middle) and type =trim(C1) where C1 is the cell reference of the first "gift from ...." and fill down (move cursor over bottom right hand corner of the cell, when you see a + double click) now select this column and copy it, then choose edit / paste special - values to change the formula into the values then delete the third column -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "S.E." wrote in message ... I would like to know if there is some code that can help me with the following. I have a column of cells with information similar to this: $50.00 Gift from Johnson, John $35.00 Gift from Public, Jimmy $75.00 Gift from Jones, Billy etc. The second part (Gift...) is not necessarily lined up vertically. I would like to move the "Gift from Johnson, John" part to a new column. Thanks for your help, Scott |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
move part of cell to new column
Julie,
Thanks for your help. That almost works for me, but not quite. Fixed Width doesn't quite do it because the columns are a little too irregular. I can't define a breaking point that divides the two columns neatly. In other words, no matter where I put the division, either some of the dollar amount ends up with Gift from or some of Gift from ends up with the dollar amount. I thought of using the G as a delimiting character. The only problem with that is that there is an occasional row that does not have the phrase "Gift from". The TRIM function is new to me. That is very useful. I am thinking that if I could trim only the left side of the column (that is, all spaces to the left of the dollar amount), then the columns would probably line up better and I could divide them based on fixed width. It looks like TRIM doesn't have that option. Do you have any other ideas for me? Thanks, Scott "JulieD" wrote in message ... Hi SE if you want to take "gift from ..." out of the original column and put it in its own column then one method is - insert two new columns to the right of your current column -select the currnt column -choose data / text to columns -choose fixed width, Next -in the little preview window click directly after the .00 in the dollar amount and before the word "gift" -click finish this should give you the dollar amounts in one column, a blank column & then the "gift .... " in the next column now click on the first line of the blank column (in the middle) and type =trim(C1) where C1 is the cell reference of the first "gift from ...." and fill down (move cursor over bottom right hand corner of the cell, when you see a + double click) now select this column and copy it, then choose edit / paste special - values to change the formula into the values then delete the third column -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ...well i'm working on it anyway "S.E." wrote in message ... I would like to know if there is some code that can help me with the following. I have a column of cells with information similar to this: $50.00 Gift from Johnson, John $35.00 Gift from Public, Jimmy $75.00 Gift from Jones, Billy etc. The second part (Gift...) is not necessarily lined up vertically. I would like to move the "Gift from Johnson, John" part to a new column. Thanks for your help, Scott |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
move part of cell to new column
Hi Scott
another option if you have at least two spaces between the .00 and the word Gift choose your data choose edit / replace in the find what box press the space bar twice in the replace with box put a * replace all then use data / text to column delimited other * and tick treat consecutive deliminators as one FINISH -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "S.E." wrote in message ... Julie, Thanks for your help. That almost works for me, but not quite. Fixed Width doesn't quite do it because the columns are a little too irregular. I can't define a breaking point that divides the two columns neatly. In other words, no matter where I put the division, either some of the dollar amount ends up with Gift from or some of Gift from ends up with the dollar amount. I thought of using the G as a delimiting character. The only problem with that is that there is an occasional row that does not have the phrase "Gift from". The TRIM function is new to me. That is very useful. I am thinking that if I could trim only the left side of the column (that is, all spaces to the left of the dollar amount), then the columns would probably line up better and I could divide them based on fixed width. It looks like TRIM doesn't have that option. Do you have any other ideas for me? Thanks, Scott "JulieD" wrote in message ... Hi SE if you want to take "gift from ..." out of the original column and put it in its own column then one method is - insert two new columns to the right of your current column -select the currnt column -choose data / text to columns -choose fixed width, Next -in the little preview window click directly after the .00 in the dollar amount and before the word "gift" -click finish this should give you the dollar amounts in one column, a blank column & then the "gift .... " in the next column now click on the first line of the blank column (in the middle) and type =trim(C1) where C1 is the cell reference of the first "gift from ...." and fill down (move cursor over bottom right hand corner of the cell, when you see a + double click) now select this column and copy it, then choose edit / paste special - values to change the formula into the values then delete the third column -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ...well i'm working on it anyway "S.E." wrote in message ... I would like to know if there is some code that can help me with the following. I have a column of cells with information similar to this: $50.00 Gift from Johnson, John $35.00 Gift from Public, Jimmy $75.00 Gift from Jones, Billy etc. The second part (Gift...) is not necessarily lined up vertically. I would like to move the "Gift from Johnson, John" part to a new column. Thanks for your help, Scott |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
move part of cell to new column
I think that will work! Thanks, your awesome!
Scott "JulieD" wrote in message ... Hi Scott another option if you have at least two spaces between the .00 and the word Gift choose your data choose edit / replace in the find what box press the space bar twice in the replace with box put a * replace all then use data / text to column delimited other * and tick treat consecutive deliminators as one FINISH -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ...well i'm working on it anyway "S.E." wrote in message ... Julie, Thanks for your help. That almost works for me, but not quite. Fixed Width doesn't quite do it because the columns are a little too irregular. I can't define a breaking point that divides the two columns neatly. In other words, no matter where I put the division, either some of the dollar amount ends up with Gift from or some of Gift from ends up with the dollar amount. I thought of using the G as a delimiting character. The only problem with that is that there is an occasional row that does not have the phrase "Gift from". The TRIM function is new to me. That is very useful. I am thinking that if I could trim only the left side of the column (that is, all spaces to the left of the dollar amount), then the columns would probably line up better and I could divide them based on fixed width. It looks like TRIM doesn't have that option. Do you have any other ideas for me? Thanks, Scott "JulieD" wrote in message ... Hi SE if you want to take "gift from ..." out of the original column and put it in its own column then one method is - insert two new columns to the right of your current column -select the currnt column -choose data / text to columns -choose fixed width, Next -in the little preview window click directly after the .00 in the dollar amount and before the word "gift" -click finish this should give you the dollar amounts in one column, a blank column & then the "gift .... " in the next column now click on the first line of the blank column (in the middle) and type =trim(C1) where C1 is the cell reference of the first "gift from ...." and fill down (move cursor over bottom right hand corner of the cell, when you see a + double click) now select this column and copy it, then choose edit / paste special - values to change the formula into the values then delete the third column -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ...well i'm working on it anyway "S.E." wrote in message ... I would like to know if there is some code that can help me with the following. I have a column of cells with information similar to this: $50.00 Gift from Johnson, John $35.00 Gift from Public, Jimmy $75.00 Gift from Jones, Billy etc. The second part (Gift...) is not necessarily lined up vertically. I would like to move the "Gift from Johnson, John" part to a new column. Thanks for your help, Scott |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
move part of cell to new column
<vbg thanks :)
-- Cheers JulieD "S.E." wrote in message ... I think that will work! Thanks, your awesome! Scott "JulieD" wrote in message ... Hi Scott another option if you have at least two spaces between the .00 and the word Gift choose your data choose edit / replace in the find what box press the space bar twice in the replace with box put a * replace all then use data / text to column delimited other * and tick treat consecutive deliminators as one FINISH -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ...well i'm working on it anyway "S.E." wrote in message ... Julie, Thanks for your help. That almost works for me, but not quite. Fixed Width doesn't quite do it because the columns are a little too irregular. I can't define a breaking point that divides the two columns neatly. In other words, no matter where I put the division, either some of the dollar amount ends up with Gift from or some of Gift from ends up with the dollar amount. I thought of using the G as a delimiting character. The only problem with that is that there is an occasional row that does not have the phrase "Gift from". The TRIM function is new to me. That is very useful. I am thinking that if I could trim only the left side of the column (that is, all spaces to the left of the dollar amount), then the columns would probably line up better and I could divide them based on fixed width. It looks like TRIM doesn't have that option. Do you have any other ideas for me? Thanks, Scott "JulieD" wrote in message ... Hi SE if you want to take "gift from ..." out of the original column and put it in its own column then one method is - insert two new columns to the right of your current column -select the currnt column -choose data / text to columns -choose fixed width, Next -in the little preview window click directly after the .00 in the dollar amount and before the word "gift" -click finish this should give you the dollar amounts in one column, a blank column & then the "gift .... " in the next column now click on the first line of the blank column (in the middle) and type =trim(C1) where C1 is the cell reference of the first "gift from ...." and fill down (move cursor over bottom right hand corner of the cell, when you see a + double click) now select this column and copy it, then choose edit / paste special - values to change the formula into the values then delete the third column -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ...well i'm working on it anyway "S.E." wrote in message ... I would like to know if there is some code that can help me with the following. I have a column of cells with information similar to this: $50.00 Gift from Johnson, John $35.00 Gift from Public, Jimmy $75.00 Gift from Jones, Billy etc. The second part (Gift...) is not necessarily lined up vertically. I would like to move the "Gift from Johnson, John" part to a new column. Thanks for your help, Scott |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Move part of cell only - help please | Excel Discussion (Misc queries) | |||
macro to move part of cell contents to another cell | Excel Discussion (Misc queries) | |||
Compare part of a cell to a column | Excel Discussion (Misc queries) | |||
How do I copy part of each cell in a column? | Excel Discussion (Misc queries) | |||
Checkbox not part of cell. (???) Cells can move "underneath". (!!!) | Excel Discussion (Misc queries) |