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.
=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...
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
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.
assuming you have 7 columns of information and you want to split after the
4th column.
--
Regards,
Tom Ogilvy