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

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

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

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

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

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