replace first two spaces with - third space with tab

[Glen_Strom]

I’m new to notepad++ advanced search & replace

I am trying to edit a text file for importing into a database table, a sample line is like this:

dd mon yyyy 1,234.56<tab>4,567.89<tab>…etc… (<tab> denotes a tab between sets of numbers, dd-mon-yyyy are real dates)

In order to properly bring dd mon yyyy in to a date field, I need it to look like dd-mon-yyyy

If I put () \b in the search field it finds the spaces I need, but also finds the space after yyyy, which I want replaced by a tab, not a dash

I need the end result to look like dd-mon-yyyy<tab>1,234.02 & leave the rest of each line alone.

I think this may be a 2 step process, first step to replace the first 2 spaces with dashes, second step to replace remaining spaces with tabs, but I’m stumped on how best to do it.

Thanks in advance.

[PeterJones]

@Glen_Strom

I think this may be a 2 step process, first step to replace the first 2 spaces with dashes, second step to replace remaining spaces with tabs

Nope. A straightforward regex can handle it in one step. my search thought would be “look for one or two digits , space, a 3-letter month, space, a four-digit year, space – capturing each of the tokens from the date into a separate capture group ; replace with group1, hyphen, group2, hyphen, group3, tab”. I would then translate that into regex-speak: FIND = (\d{1,2}) (\w+) (\d{4})\x20 (I used \x20 for the final space because it is easier to read/copy/paste in the forum; in your own regex, that could equally be a space character at the end of the FIND instead), REPLACE = ${1}-${2}-${3}\t, SEARCH MODE = regular expression.

That converts

11 mon 2021 1,234.56<tab>4,567.89<tab>…etc
21 xxx 1999 1,234.56<tab>4,567.89<tab>…etc

into

11-mon-2021	1,234.56<tab>4,567.89<tab>…etc
21-xxx-1999	1,234.56<tab>4,567.89<tab>…etc

which I think is what you want

----

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 literal text using the </> toolbar button or manual Markdown syntax. To make regex in red (and so they keep their special characters like *), use backticks, like `^.*?blah.*?\z`. 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.

[Glen_Strom]

@PeterJones said in replace first two spaces with - third space with tab:

${1}-${2}-${3}\t

Thanks so much, exactly what I was looking for!

Now that I have it done I’ll look closer at your solution & figure out what it all means.

It turns out the text file was a little wonky, all of the months but September were 3 letter, and the rest already had a tab like I wanted after the 4 digit year, but the September months had a space. So it was like 31<sp>Aug<sp> 2021<tab>1234.56 and 01<sp>Sept<sp>2021<sp>6789.12 - so it was just a matter of replacing all the Sept with Sep, then running your solution on them, then I ran the () \b in the search with - in the replace for the rest of them.

In any case, I was able to generate an insert statement & got all my rows into my database table, so thanks again!