Sort Text into Rows by Multiple Delimiters/Values

[BoostThis]

Hello Excel Forum -

I am trying to consolidate an .835 raw text file that is full of receipts and payments into rows by specific values/characters. Once opened within notepad/text format, I figured out that the ~CLP is the referencing value/character used to break up each individual payment.

I need to somehow use the ~CLP as the value(s) to sort the text into new rows, with each row pre-leading with the ~CLP.

For example:

~CLP*442.24*DTM*B6*PR~CLP*124.32...etc

New Row format needed:

~CLP*442.24*DTM*B6*PR
~CLP*124.32

Please let me know if more information is needed.

Thank you in advance.

[vlady]

Data -> Text to column -> delimeted -> unctick all except the "other" type in the box ~ -> next-...
that is Tilde just above the tab button in the keyboard
now select the data click copy -> click a blank cell below ->paste special -> Transpose -> Ok

[Ghozi Alkatiri]

ie data at A1 formula start at B1

=TRIM(MID(SUBSTITUTE(A$1,"~",REPT(" ",100)&"~"),ROW(1:1)*100+1,100))

copy down

[BoostThis]

Data -> Text to column -> delimeted -> unctick all except the "other" type in the box ~ -> next-...
that is Tilde just above the tab button in the keyboard
now select the data click copy -> click a blank cell below ->paste special -> Transpose -> Ok

Thank you vlady. However, this will not work due to the fact that the text has multiple tilde symbols randomly throughout the file. The ~CLP is the only unique identifier I have to seperate what should truly be an individual row.

I cannot also just use ~C because there are other lines that have ~CAS. THE ~CLP is the only unique identifier I have.

[vlady]

just to answer using TTC

use find and replace first
find: ~~CLP
replace: (alt+177)CLP (any alt key combination could suffice as long as it's not in the text/string itself )
then Replace ALL

then use that alt combination in the text-to-column

[BoostThis]

just to answer using TTC

use find and replace first
find: ~~CLP
replace: (alt+177)CLP (any alt key combination could suffice as long as it's not in the text/string itself )
then Replace ALL

then use that alt combination in the text-to-column

Once again, thank you. Unfortunately, I'm beginning to come to the conclusion that because the raw text is not already sorted into rows before going into Excel, I may need a VBS to re-sort the file based on the ~CLP values/characters. Once this is complete and my text is in concurrent rows, I should then be able to import it into Excel, use Find and Replace against the ~CLP, and then break out the data using TTC.

Thank you once again vlady.

[BoostThis]

In case anyone is curious, here is the VB script I used. It took about 10 minutes to re-sort the text data:

Const Path = "C:\Temp\all.txt"
Const outPath = "C:\Temp\out.txt"


With CreateObject("Scripting.FileSystemObject")
Set inFile = .OpenTextFile(Path)
Set outFile = .OpenTextFile(outPath, 2, True)
End With

count = 0
Loop_count = 0
sub_count = 1
start_pos = 1
whole_msg = inFile.ReadAll
'msgbox(len(whole_msg))
Do Until start_pos >= len(whole_msg)
'msgbox(whole_msg)
start_msg = Instr(start_pos,Cstr(whole_msg), "~CLP")
'msgbox(start_msg)
end_msg = Instr(start_Msg, Cstr(whole_msg), "~CAS")-1
'msgbox(end_msg)
msg = mid(Cstr(whole_msg),start_msg,end_msg - start_msg)
start_pos = end_msg +2
'msgbox(start_pos)
'msgbox(msg)
outFile.WriteLine msg
count = count + 1



Loop_count = Loop_count + 1

If (Loop_count = 15000*sub_count) Then
msgbox(Loop_count)
msgbox(count)
sub_count = sub_count + 1
End if

Loop

function ReadList(listfile)

const forReading = 1
dim thelist()
redim thelist(1)
listLen = 0
set theFSO = createobject("Scripting.FileSystemObject")
set listFile = theFSo.openTextFile(listfile,forReading)

while not listFile.atendofstream
pcname = ltrim(rtrim(listFile.readline))
if len(pcname)>1 and left(pcname,1)<>";" then
if listlen = 0 then
thelist(0) = pcname
listlen = listlen+1
else
redim preserve thelist(listlen)
thelist(listlen) = pcname
listlen = listlen + 1
end if
end if
wend

listfile.close

set listfile = nothing
set thefso = nothing

ReadList = theList

end Function