Cut and paste by reference

[pnasc...@gmail.com]

Hi,
I have some information on many really long lines at Excel. I'd like to
cut some of it (based on a 1-long cell with a single "T" on it) and
insert on a new line, right below it.
I tried to record a macro and see how does excel does it, but it uses
an absolute (Range("A2")) reference, not a relative. Also, it can't be
done on the new line, because it'll erase data from the line below.

A scrap of what's needed:

P|00000|ICMS|IPI|T|123|999
P|00001|ABCD|ASD|T|456|666
P|00002|LINK|LUNK|T|789|333

would become:

P|00000|ICMS|IPI
T|123|999
P|00001|ABCD|ASD
T|456|666
P|00002|LINK|LUNK
T|789|333

Thanks in advance.

[Daniel CHEN]

The following is a formula solution, not use VBA code:
Suppose you have original text in Column A staring from A1 and you store
results in Column B, starting from B1.
In Column B, type the following formula - all cells B1, B2, B3, etc. have
the same formula

=IF(MOD(ROW(),2)=1,LEFT(INDIRECT(ADDRESS(INT(ROW()/2+0.5),1)),FIND("|T|",INDIRECT(ADDRESS(INT(ROW()/2+0.5),1)))-1),SUBSTITUTE(INDIRECT(ADDRESS(INT(ROW()/2+0.5),1)),INDIRECT(ADDRESS(ROW()-1,2))&"|",""))

The formula is very long but it works as following:

P|00000|ICMS|IPI|T|123|999 P|00000|ICMS|IPI
P|00001|ABCD|ASD|T|456|666 T|123|999
P|00002|LINK|LUNK|T|789|333 P|00001|ABCD|ASD

T|456|666
P|00002|LINK|LUNK
T|789|333

Hope this solve your problem.

--
Best regards,
---
Yongjun CHEN
=================================
XLDataSoft - Data Analysis Expert, Excel/VBA Specialist
- - - - www.XLDataSoft.com - - - -
Free Excel-Based Data Processing Tool is Available for Download
Free Excel/VBA Training Materials is Available for Downloand
=================================

<pnasc...@gmail.com> wrote in message
news:1145556927....@g10g2000cwb.googlegroups.com...

[Tom Ogilvy]

With your sample data in A1:A3 (assumes each row you show is a single string
in column A of that row) this produced what you show:

Sub BreakSTrings()
Dim lastrow As Long, rng As Range
Dim iloc As Long

lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = lastrow + 1 To 2 Step -1
Set rng = Cells(i - 1, 1)
iloc = InStr(1, rng, "|T|", vbTextCompare)
If iloc > 0 Then
Rows(i).Insert
Cells(i, 1) = Mid(rng, iloc + 1, 255)
rng.Value = Left(rng, iloc - 1)
End If
Next
End Sub

--
Regards,
Tom Ogilvy

[Lunks]

Actually, I wasn't very clear.

Each time a | is shown, I actually meant a cell break.
So, A1 would have only "P". A2, "00000" and so on.

Tom, I ran your macro but nothing happened. I'm not very used to VBA,
so I really can't tell what it does neither how to modify it to my
needs, sorry.

Help is really aprecciated.

[Tom Ogilvy]

Sub DDDD()
Dim i As Long
Dim lastrow As Long
Dim rng As Range
lastrow = Cells(Rows.Count, 1 _

).End(xlUp).Row
For i = lastrow + 1 To 2 Step -1
Set rng = Cells(i - 1, 1)

Rows(i).Insert
Cells(i - 1, 5).Resize(1, 3).Copy Cells(i, 1)
Cells(i - 1, 5).Resize(1, 3).ClearContents
Next
End Sub

assuming you have 7 columns of information and you want to split after the
4th column.

--
Regards,
Tom Ogilvy

[Lunks]

Thanks, it works!
But I can't figure how to change which cells will go to the new line. I
just can't figure it out from the code.