Help formatting text!? Possible in notepad++?

brandon schwartz

I have a range of data that was supposed to be formatted as Part#|All prices, however the prices got listed vertically in a list.

The data was supposed to look like this: Part123 | 10$ 11$ 12$ 13$

But it looks like this Part123|10$
11$
12$

I quickly figured out that there didn’t seem to be a method to fix this via excel so I opened it in notepad and got.

Part123
10$
11$
12$
13$
Part456
14$
15$
16$

What I need is the average of all prices and the lowest price from every set. I have about 3,000 part numbers. Is there any way to automatically format this?

Alan Kilborn

@brandon-schwartz said:

The data was supposed to look like this: Part123 | 10$ 11$ 12$ 13$

You could likely recover the above formatting, but Notepad++ can’t help you with:

the average of all prices and the lowest price from every set

Terry R

@brandon-schwartz said:

The data was supposed to look like this: Part123 | 10$ 11$ 12$ 13$

As @Alan-Kilborn said Notepad++ can help with fixing the formatting (see my regex below), but cannot help with calculations as Notepad++ itself does NOT have a mathematical ability.

So to get the formatting back to how you’d like it my regex is:
Find What:(?-s)(part\d+$)|(\R(\d+\$))
Replace With:(?1\1 \|)(?3 \3)

As this is a regex you need the search mode in “regular expression”. Once the 2 fields are entered (can copy the red text and paste) just pres the “Replace All” button once, the whole file should be reformatted. Suggest have the cursor in the very first position of the first line, otherwise the “wrap around” would need to be enabled (most have that enabled already anyways).

As a bit of background, the regex uses alternation. Thus it can search for different types of text and the replace field can interpret which option was selected and based on that respond with a different set of replacements. You will note that some of the characters have a \ in front, and sometimes not. That’s because I cam using the characters in 2 different modes. For example the $ by itself means a zero length position at the end of a line, as \$ it means literally a dollar sign.

Good luck

Terry