I have a sheet of data where in column C I want to insert Chr10 in
each cell at the point where if I'm doing it manually I F2 to edit the
cell then Ctrl L Arrow thrice...

I've tried recording a Macro to do this but it just includes the text
of the particular cell that I'm currently editing and inserts the
Chr10 in where I have done it manually without telling me how to do
what I want....

I'd like to do it with a Macro as I've 1700ish rows to do and am
starting to get RSI from it :(

Eventually this will be saved as a CSV file so there'll be nothing
Excel in it...

Any help gratefully received :)

Nick

Let's pick a cell and enter:

go to alpha ralpha blvd

If we select the cell and touch F2 and then CNTRL-BACKARROW three times, the
editting cursor will be just before the alpha and this is where the Chr(10)
will go. Try this simple macro:

Sub batman()
For Each r In Selection
s = Split(r.Value, " ")
s(UBound(s) - 2) = Chr(10) & s(UBound(s) - 2)
r.Value = Join(s, " ")
Next
End Sub

--
Gary''s Student - gsnu2007i


"NickTheBatMan" wrote:

> I have a sheet of data where in column C I want to insert Chr10 in
> each cell at the point where if I'm doing it manually I F2 to edit the
> cell then Ctrl L Arrow thrice...
>
> I've tried recording a Macro to do this but it just includes the text
> of the particular cell that I'm currently editing and inserts the
> Chr10 in where I have done it manually without telling me how to do
> what I want....
>
> I'd like to do it with a Macro as I've 1700ish rows to do and am
> starting to get RSI from it :(
>
> Eventually this will be saved as a CSV file so there'll be nothing
> Excel in it...
>
> Any help gratefully received :)
>
> Nick
>

Mm, either I've got the wrong end of this or it don't do what I was
hoping for...

It's putting Chr10 - a CR - at the beginning of the cell, or if I
select several cells it progresses to the next cell and puts the next
space in the text until it runs out of text then it fails...

Here's some of the text from the cells: -

WHITEHOUSE FPS-DSN2-015-1672
WEST DYKE ROAD MCG-DSN3-022-1562
CHURCH LANE CCTV-DSN3-023-0440
GREEN LANE FPS-DSN3-024-1100
GREW GRASS UWC-DSN3-025-0110

As you can see there's not equal words to begin with, I want the CR
before the group that is held together by the - hyphen...

Nick

On 26 May, 17:28, Gary''s Student
<GarysStud...@discussions.microsoft.com> wrote:
> Let's pick a cell and enter:
>
> go to alpha ralpha blvd
>
> If we select the cell and touch F2 and then CNTRL-BACKARROW three times, the
> editting cursor will be just before the alpha and this is where the Chr(10)
> will go. Try this simple macro:
>
> Sub batman()
> For Each r In Selection
> s = Split(r.Value, " ")
> s(UBound(s) - 2) = Chr(10) & s(UBound(s) - 2)
> r.Value = Join(s, " ")
> Next
> End Sub
>
> --
> Gary''s Student - gsnu2007i
>
> "NickTheBatMan" wrote:
> > I have a sheet of data where in column C I want to insert Chr10 in
> > each cell at the point where if I'm doing it manually I F2 to edit the
> > cell then Ctrl L Arrow thrice...
>
> > I've tried recording a Macro to do this but it just includes the text
> > of the particular cell that I'm currently editing and inserts the
> > Chr10 in where I have done it manually without telling me how to do
> > what I want....
>
> > I'd like to do it with a Macro as I've 1700ish rows to do and am
> > starting to get RSI from it :(
>
> > Eventually this will be saved as a CSV file so there'll be nothing
> > Excel in it...
>
> > Any help gratefully received :)
>
> > Nick

Have patience...we are making good progress. Your examples removed part of
my confusion. We clearly do not need the -2

Try this new version instead:

Sub batman()
For Each r In Selection
s = Split(r.Value, " ")
s(UBound(s)) = Chr(10) & s(UBound(s))
r.Value = Join(s, " ")
Next
End Sub


1. replace the macro
2. select the cells
3. run the macro

Post back if more modifications are needed.
--
Gary''s Student - gsnu2007i


"NickTheBatMan" wrote:

> Mm, either I've got the wrong end of this or it don't do what I was
> hoping for...
>
> It's putting Chr10 - a CR - at the beginning of the cell, or if I
> select several cells it progresses to the next cell and puts the next
> space in the text until it runs out of text then it fails...
>
> Here's some of the text from the cells: -
>
> WHITEHOUSE FPS-DSN2-015-1672
> WEST DYKE ROAD MCG-DSN3-022-1562
> CHURCH LANE CCTV-DSN3-023-0440
> GREEN LANE FPS-DSN3-024-1100
> GREW GRASS UWC-DSN3-025-0110
>
> As you can see there's not equal words to begin with, I want the CR
> before the group that is held together by the - hyphen...
>
> Nick
>
> On 26 May, 17:28, Gary''s Student
> <GarysStud...@discussions.microsoft.com> wrote:
> > Let's pick a cell and enter:
> >
> > go to alpha ralpha blvd
> >
> > If we select the cell and touch F2 and then CNTRL-BACKARROW three times, the
> > editting cursor will be just before the alpha and this is where the Chr(10)
> > will go. Try this simple macro:
> >
> > Sub batman()
> > For Each r In Selection
> > s = Split(r.Value, " ")
> > s(UBound(s) - 2) = Chr(10) & s(UBound(s) - 2)
> > r.Value = Join(s, " ")
> > Next
> > End Sub
> >
> > --
> > Gary''s Student - gsnu2007i
> >
> > "NickTheBatMan" wrote:
> > > I have a sheet of data where in column C I want to insert Chr10 in
> > > each cell at the point where if I'm doing it manually I F2 to edit the
> > > cell then Ctrl L Arrow thrice...
> >
> > > I've tried recording a Macro to do this but it just includes the text
> > > of the particular cell that I'm currently editing and inserts the
> > > Chr10 in where I have done it manually without telling me how to do
> > > what I want....
> >
> > > I'd like to do it with a Macro as I've 1700ish rows to do and am
> > > starting to get RSI from it :(
> >
> > > Eventually this will be saved as a CSV file so there'll be nothing
> > > Excel in it...
> >
> > > Any help gratefully received :)
> >
> > > Nick
>
>

I'm not sure if it is important to the OP or not, but your routine leaves a
trailing space at the end the first line formed by the split. I guess you
could use the Replace function like this to remove the trailing space...

Sub batman()
Dim r As Range
Dim s As Variant
For Each r In Selection
s = Split(r.Value, " ")
s(UBound(s)) = Chr(10) & s(UBound(s))
r.Value = Join(s, " ")
r.Value = Replace(r.Value, " " & Chr(10), Chr(10))
Next
End Sub

Here is an alternate subroutine which, as a by product of how it works, does
not introduce the trailing space in the first place...

Sub AddCR()
Dim Cell As Range
Dim Contents As String
For Each Cell In Range("A1:A5")
Contents = Cell.Text
Mid$(Contents, InStrRev(Contents, " ")) = vbLf
Cell.Value = Contents
Next
End Sub

Note my preference for the predefined VB constant vbLf instead of the
Chr(10) function call.

Rick


"Gary''s Student" <GarysStudent@discussions.microsoft.com> wrote in message
news:AFD80B0E-9E5E-4E31-BF24-70BFD38FD84B@microsoft.com...
> Have patience...we are making good progress. Your examples removed part
> of
> my confusion. We clearly do not need the -2
>
> Try this new version instead:
>
> Sub batman()
> For Each r In Selection
> s = Split(r.Value, " ")
> s(UBound(s)) = Chr(10) & s(UBound(s))
> r.Value = Join(s, " ")
> Next
> End Sub
>
>
> 1. replace the macro
> 2. select the cells
> 3. run the macro
>
> Post back if more modifications are needed.
> --
> Gary''s Student - gsnu2007i
>
>
> "NickTheBatMan" wrote:
>
>> Mm, either I've got the wrong end of this or it don't do what I was
>> hoping for...
>>
>> It's putting Chr10 - a CR - at the beginning of the cell, or if I
>> select several cells it progresses to the next cell and puts the next
>> space in the text until it runs out of text then it fails...
>>
>> Here's some of the text from the cells: -
>>
>> WHITEHOUSE FPS-DSN2-015-1672
>> WEST DYKE ROAD MCG-DSN3-022-1562
>> CHURCH LANE CCTV-DSN3-023-0440
>> GREEN LANE FPS-DSN3-024-1100
>> GREW GRASS UWC-DSN3-025-0110
>>
>> As you can see there's not equal words to begin with, I want the CR
>> before the group that is held together by the - hyphen...
>>
>> Nick
>>
>> On 26 May, 17:28, Gary''s Student
>> <GarysStud...@discussions.microsoft.com> wrote:
>> > Let's pick a cell and enter:
>> >
>> > go to alpha ralpha blvd
>> >
>> > If we select the cell and touch F2 and then CNTRL-BACKARROW three
>> > times, the
>> > editting cursor will be just before the alpha and this is where the
>> > Chr(10)
>> > will go. Try this simple macro:
>> >
>> > Sub batman()
>> > For Each r In Selection
>> > s = Split(r.Value, " ")
>> > s(UBound(s) - 2) = Chr(10) & s(UBound(s) - 2)
>> > r.Value = Join(s, " ")
>> > Next
>> > End Sub
>> >
>> > --
>> > Gary''s Student - gsnu2007i
>> >
>> > "NickTheBatMan" wrote:
>> > > I have a sheet of data where in column C I want to insert Chr10 in
>> > > each cell at the point where if I'm doing it manually I F2 to edit
>> > > the
>> > > cell then Ctrl L Arrow thrice...
>> >
>> > > I've tried recording a Macro to do this but it just includes the text
>> > > of the particular cell that I'm currently editing and inserts the
>> > > Chr10 in where I have done it manually without telling me how to do
>> > > what I want....
>> >
>> > > I'd like to do it with a Macro as I've 1700ish rows to do and am
>> > > starting to get RSI from it :(
>> >
>> > > Eventually this will be saved as a CSV file so there'll be nothing
>> > > Excel in it...
>> >
>> > > Any help gratefully received :)
>> >
>> > > Nick
>>
>>

On 26 May, 20:17, Gary''s Student
<GarysStud...@discussions.microsoft.com> wrote:
> Have patience...we are making good progress. Your examples removed part of
> my confusion. We clearly do not need the -2
>
> Try this new version instead:
>
> Sub batman()
> For Each r In Selection
> s = Split(r.Value, " ")
> s(UBound(s)) = Chr(10) & s(UBound(s))
> r.Value = Join(s, " ")
> Next
> End Sub
>
> 1. replace the macro
> 2. select the cells
> 3. run the macro
>
> Post back if more modifications are needed.
> --
> Gary''s Student - gsnu2007i
>
> "NickTheBatMan" wrote:
> > Mm, either I've got the wrong end of this or it don't do what I was
> > hoping for...
>
> > It's putting Chr10 - a CR - at the beginning of the cell, or if I
> > select several cells it progresses to the next cell and puts the next
> > space in the text until it runs out of text then it fails...
>
> > Here's some of the text from the cells: -
>
> > WHITEHOUSE FPS-DSN2-015-1672
> > WEST DYKE ROAD MCG-DSN3-022-1562
> > CHURCH LANE CCTV-DSN3-023-0440
> > GREEN LANE FPS-DSN3-024-1100
> > GREW GRASS UWC-DSN3-025-0110
>
> > As you can see there's not equal words to begin with, I want the CR
> > before the group that is held together by the - hyphen...
>
> > Nick
>
> > On 26 May, 17:28, Gary''s Student
> > <GarysStud...@discussions.microsoft.com> wrote:
> > > Let's pick a cell and enter:
>
> > > go to alpha ralpha blvd
>
> > > If we select the cell and touch F2 and then CNTRL-BACKARROW three times, the
> > > editting cursor will be just before the alpha and this is where the Chr(10)
> > > will go. Try this simple macro:
>
> > > Sub batman()
> > > For Each r In Selection
> > > s = Split(r.Value, " ")
> > > s(UBound(s) - 2) = Chr(10) & s(UBound(s) - 2)
> > > r.Value = Join(s, " ")
> > > Next
> > > End Sub
>
> > > --
> > > Gary''s Student - gsnu2007i
>
> > > "NickTheBatMan" wrote:
> > > > I have a sheet of data where in column C I want to insert Chr10 in
> > > > each cell at the point where if I'm doing it manually I F2 to edit the
> > > > cell then Ctrl L Arrow thrice...
>
> > > > I've tried recording a Macro to do this but it just includes the text
> > > > of the particular cell that I'm currently editing and inserts the
> > > > Chr10 in where I have done it manually without telling me how to do
> > > > what I want....
>
> > > > I'd like to do it with a Macro as I've 1700ish rows to do and am
> > > > starting to get RSI from it :(
>
> > > > Eventually this will be saved as a CSV file so there'll be nothing
> > > > Excel in it...
>
> > > > Any help gratefully received :)
>
> > > > Nick

Weeeeeeeeeeeeee :) :) :)

Worked like a dream :) :) :)

Wish I could understand how to do all this clever stuff.........

VMT

Nick

Many thanks for all that Rick, it doesn't matter about the space at
the end of the first line...

Didn't know about vblf, not sure if it's necessary as chr10's doing
what I want anyway... :)

For info all, this is me creating Points Of Interest layer for my
Garmin GPS so that I can upload it and it looks good on the device :)

It's worked so far...

Nick

On 26 May, 21:34, "Rick Rothstein \(MVP - VB\)"
<rick.newsNO.S...@NO.SPAMverizon.net> wrote:
> I'm not sure if it is important to the OP or not, but your routine leaves a
> trailing space at the end the first line formed by the split. I guess you
> could use the Replace function like this to remove the trailing space...
>
> Sub batman()
> Dim r As Range
> Dim s As Variant
> For Each r In Selection
> s = Split(r.Value, " ")
> s(UBound(s)) = Chr(10) & s(UBound(s))
> r.Value = Join(s, " ")
> r.Value = Replace(r.Value, " " & Chr(10), Chr(10))
> Next
> End Sub
>
> Here is an alternate subroutine which, as a by product of how it works, does
> not introduce the trailing space in the first place...
>
> Sub AddCR()
> Dim Cell As Range
> Dim Contents As String
> For Each Cell In Range("A1:A5")
> Contents = Cell.Text
> Mid$(Contents, InStrRev(Contents, " ")) = vbLf
> Cell.Value = Contents
> Next
> End Sub
>
> Note my preference for the predefined VB constant vbLf instead of the
> Chr(10) function call.
>
> Rick
>
> "Gary''s Student" <GarysStud...@discussions.microsoft.com> wrote in message
>
> news:AFD80B0E-9E5E-4E31-BF24-70BFD38FD84B@microsoft.com...
>
> > Have patience...we are making good progress. Your examples removed part
> > of
> > my confusion. We clearly do not need the -2
>
> > Try this new version instead:
>
> > Sub batman()
> > For Each r In Selection
> > s = Split(r.Value, " ")
> > s(UBound(s)) = Chr(10) & s(UBound(s))
> > r.Value = Join(s, " ")
> > Next
> > End Sub
>
> > 1. replace the macro
> > 2. select the cells
> > 3. run the macro
>
> > Post back if more modifications are needed.
> > --
> > Gary''s Student - gsnu2007i
>
> > "NickTheBatMan" wrote:
>
> >> Mm, either I've got the wrong end of this or it don't do what I was
> >> hoping for...
>
> >> It's putting Chr10 - a CR - at the beginning of the cell, or if I
> >> select several cells it progresses to the next cell and puts the next
> >> space in the text until it runs out of text then it fails...
>
> >> Here's some of the text from the cells: -
>
> >> WHITEHOUSE FPS-DSN2-015-1672
> >> WEST DYKE ROAD MCG-DSN3-022-1562
> >> CHURCH LANE CCTV-DSN3-023-0440
> >> GREEN LANE FPS-DSN3-024-1100
> >> GREW GRASS UWC-DSN3-025-0110
>
> >> As you can see there's not equal words to begin with, I want the CR
> >> before the group that is held together by the - hyphen...
>
> >> Nick
>
> >> On 26 May, 17:28, Gary''s Student
> >> <GarysStud...@discussions.microsoft.com> wrote:
> >> > Let's pick a cell and enter:
>
> >> > go to alpha ralpha blvd
>
> >> > If we select the cell and touch F2 and then CNTRL-BACKARROW three
> >> > times, the
> >> > editting cursor will be just before the alpha and this is where the
> >> > Chr(10)
> >> > will go. Try this simple macro:
>
> >> > Sub batman()
> >> > For Each r In Selection
> >> > s = Split(r.Value, " ")
> >> > s(UBound(s) - 2) = Chr(10) & s(UBound(s) - 2)
> >> > r.Value = Join(s, " ")
> >> > Next
> >> > End Sub
>
> >> > --
> >> > Gary''s Student - gsnu2007i
>
> >> > "NickTheBatMan" wrote:
> >> > > I have a sheet of data where in column C I want to insert Chr10 in
> >> > > each cell at the point where if I'm doing it manually I F2 to edit
> >> > > the
> >> > > cell then Ctrl L Arrow thrice...
>
> >> > > I've tried recording a Macro to do this but it just includes the text
> >> > > of the particular cell that I'm currently editing and inserts the
> >> > > Chr10 in where I have done it manually without telling me how to do
> >> > > what I want....
>
> >> > > I'd like to do it with a Macro as I've 1700ish rows to do and am
> >> > > starting to get RSI from it :(
>
> >> > > Eventually this will be saved as a CSV file so there'll be nothing
> >> > > Excel in it...
>
> >> > > Any help gratefully received :)
>
> >> > > Nick