This is too much for me! I need to create a macro to extract separate names from all the cells in a table in order to find eventual transcription mistakes more easily. Can someone help me? Many thanks in advance.
As a workaround:
Why not use menu Data - Text to Columns? Separate by space.
Do it one column at a time (better, put all names in one column).
Copy all of them, paste in Writer, replace tab (\t) with enter (\n).
Now you can copy and paste again in Calc as one column (Like Resultado in your screenshot).
1 Like
Whaaat? Oh, my! I have completely forgotten how I do love Excel. Of course you’re right, but we dont have to paste anything in Write, we can do it all in Excel/Calc: Data > More filters > Options > No duplicates. Thank you for the tip, anyways. Let’s forget about the macro and do it all by hand.
@Quaresma Just an addition to the solution. You can make this job a little easier if you start by entering a formula like
=SUBSTITUTE(TEXTJOIN(" ";1;A2:B20);" ";CHAR(10)) into D2
In other words, concatenate the texts of all non-blank cells with a “space” delimiter and replace all spaces in the resulting string with “line feed”. And only then for column D apply Data - Text to Columns, Sort and Standard filter - No duplicates
1 Like
Just for fun:
import pyperclip as clip
import re
rex = re.compile(r"[\s,.()-]+")
def norm_names_to_clip(*_):
doc = XSCRIPTCONTEXT.getDocument()
data = doc.CurrentSelection.DataArray
out = set()
for row in data:
for entry in row:
out.update(rex.split(entry))
out = "\n".join(sorted(out))
clip.copy(out)
depend on third-party »pyperclip« > pip install pyperclip
usage: select the range of Names
run above norm_names_to_clip
select target cell and paste with <strg><v>
You’re probably right, Karolus, but that is still too much for me. I would need more information to put it working properly. Thank you for your trouble.