CODEWITHSUNDEEP
excelcsvvim
Skeates
Skeates

Reputation: 47

unique search and replace

I have a csv with two columns. If I import this csv into excel I end up with column A and column B.

Column A contains a string of text eg: 1234 (each row has a unique value)
column B contains a string of text eg: abcd1234

I need to remove the text from column B that is contained in column A. Each row has unique data so an example would look like so:

1234, abcd1234,
5678, efgh5678,
9876, ijkl9876,

What I want to end up with is:

1234, abcd,
5678, efgh,
9876, ijkl,

Any ideas I'm happy to process in excel of with command line.

Upvotes: 0

Views: 45

Answers (1)

Ingo Karkat
Ingo Karkat

Reputation: 172600

In Vim, you can do this via :substitute, capturing the first column, and those parts of the second column that don't match the first value (referenced via \1). I'll let the match start on the second column (with \zs); this avoids re-referencing the first column in the replacement part, which just consists of the stuff before (\2; not present in your example text), and after (\3) the repeated part from column 1.

:%substitute/^\([^,]\+\),\zs\([^,]*\)\1\([^,]*\)/\2\3/

The [^,] stands for any characters inside a column, assuming a simple CSV format.

Upvotes: 1

Related Questions