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

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." <[email protected]> wrote in message
news:%23tOBH2%[email protected]...
>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
>

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" <[email protected]> wrote in message
news:%23Zu5V7%[email protected]...
> 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." <[email protected]> wrote in message
> news:%23tOBH2%[email protected]...
>>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
>>
>
>

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." <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:%23Zu5V7%[email protected]...
>> 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." <[email protected]> wrote in message
>> news:%23tOBH2%[email protected]...
>>>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
>>>
>>
>>
>
>

I think that will work! Thanks, your awesome!

Scott

"JulieD" <[email protected]> wrote in message
news:%[email protected]...
> 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." <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:%23Zu5V7%[email protected]...
>>> 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." <[email protected]> wrote in message
>>> news:%23tOBH2%[email protected]...
>>>>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
>>>>
>>>
>>>
>>
>>
>
>

<vbg> thanks

--
Cheers
JulieD

"S.E." <[email protected]> wrote in message
news:[email protected]...
>I think that will work! Thanks, your awesome!
>
> Scott
>
> "JulieD" <[email protected]> wrote in message
> news:%[email protected]...
>> 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." <[email protected]> wrote in message
>> news:[email protected]...
>>> 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" <[email protected]> wrote in message
>>> news:%23Zu5V7%[email protected]...
>>>> 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." <[email protected]> wrote in message
>>>> news:%23tOBH2%[email protected]...
>>>>>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
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>