0
I'm trying to organize catalog records exported from a very outdated program my library is using to get it ready to import into the new catalog. The records come out like this:
~#[K11
title[Yada Yada
date[19xx
Entry body text
Entry body text
Volume:1
Location: Outer Mongolia
]
And I'd like them to look like this all on one line transposed:
~#[K11 title[Yada Yada date[19xx Entry body text Entry body text Volume:1 Location: Outer Mongolia ]
The records may or may not have all fields, but they all start with '#[' and they all end with ']'. Since these are the only times these symbols show up I was trying to write a macro that would go down column A look for those symbols, and transpose everything between them. But I'm not good enough, any help would be greatly appreciated!
Edit: I am starting from code as answered so nicely by @Excellll in another post and this is where I am so far:
Dim n As Long
n = 30000
For i = 1 To n Step 5
Range("A1:A5").Offset(i - 1, 0).Select
Selection.Copy
Range("B10").Offset((i - 1) / 5 + 1, 0).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Next I
However, each entry is not 5 lines long so I can't use Step 5
, also my data is not contiguous so I can't use COUNTA
.
Any suggestions for a step of variable size between the two specific characters?
You have the right idea, you will need a macro to loop through all the data and reformat it but if you don't have anything to start with, you are kinda just asking someone to do it all for you. – unknownSPY – 2017-01-25T18:12:08.337
I have how to transpose the data when I manually selected it:
code
Selection.Copy ActiveCell.Offset(0, 2).Range("A1").Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True End Subcode
Now I'm working on how to define the selection as everything between '#[.....]' . – Sarah P – 2017-01-25T18:29:26.860What is the format of your data? If it's .csv then I'd consider to format the data in another tool (e.g. Notepad++) using regex. That's much easier. – Máté Juhász – 2017-01-26T09:23:56.980