Batch Replacment

zlab dxb

Hi Good Day,

May I know how I can do follwing with notepad?

I have an excel file conatining data in Coulem A and Column B
I have some text data
I want to search in text data and replace matching entries/data of Column A with data of Column B

Terry R

I have an excel file

Well, if it’s an XLSX (XLSM or similar) file then Notepad++ can’t open it, it will cause a error with the file if edited in Notepad++. If however it’s a CSV (comma separated values) file then yes Notepad++ can edit it.

What you will need to do (if a CSV file) is provide an example of the type of data. Select a few rows (lines) from the file and insert them into your next post. Then select them and hit the </> button just above the post window. This will highlight the examples within a black box which prevents the posting engine from otherwise altering the data.

If you can repeat the examples in a separate black window and show the changes visually, as well as explain in good detail that will also help. So a “before” and “after” window of examples.

Terry

PeterJones

@zlab-dxb ,

Notepad++ is a text editor, not a spreadsheet application. If you have an Excel file (.xlsx or similar extension), Notepad++ was not designed to handle that binary format, because Notepad++ is a text editor. I might recommend LibreOffice Calc or OpenOffice Calc as a free, open source spreadsheet program.

If what you have is a CSV file, then Notepad++ could theoretically open it, but so could the aforementioned free, open source spreadsheet programs. And oddly enough, spreadsheet programs are built to handle spreadsheets quite well.

If you really insist on editing a CSV spreadsheet in Notepad++, it could be done, though how complicated depends on your data, which you didn’t bother showing (if there is a comma in either column A or column B, things just got more complicated than I am going to help with)

For example, if you want to replace TWO from column A with whatever is in column B for that row in the CSV below:

COLUMN A,COLUMN B,COLUMN C
ONE, 1.618, phi
TWO, 2.718281828, e
THREE, 3.14159, 
TWO, 2.0
END

You could use

FIND = (?-s)^TWO, (.*?)(?=,|$)
REPLACE = $1, $1
Mode = regular expression
REPLACE (twice) or REPLACE ALL (once)

COLUMN A,COLUMN B,COLUMN C
ONE, 1.618, phi
2.718281828, 2.718281828, e
THREE, 3.14159, 
2.0, 2.0
END

@Terry-R beat me to most of the overhead/caveats. I included one possible example to show how it might be done; with a simple CSV, you’d just have to use whatever data you wanted to match from COLUMN A instead of TWO. But depending on your data, it will be more complicated than I showed.

Please follow the advice below if you want help customizing this for your data.

----

Do you want regex search/replace help? Then please be patient and polite, show some effort, and be willing to learn; answer questions and requests for clarification that are made of you. All example text should be marked as plain text using the </> toolbar button or manual Markdown syntax. Screenshots can be pasted from the clipboard to your post using Ctrl+V to show graphical items, but any text should be included as literal text in your post so we can easily copy/paste your data. Show the data you have and the text you want to get from that data; include examples of things that should match and be transformed, and things that don’t match and should be left alone; show edge cases and make sure you examples are as varied as your real data. Show the regex you already tried, and why you thought it should work; tell us what’s wrong with what you do get… Read the official NPP Searching / Regex docs and the forum’s Regular Expression FAQ. If you follow these guidelines, you’re much more likely to get helpful replies that solve your problem in the shortest number of tries.