How to (auto) format text to start at column 25, 85 and 115
-
I need to submit personal data using Notepad++ in a specific format. The required format is as follows: initials, last name, and date of birth. In Notepad++, the formatting should ensure that for each person, the initial is placed in column 25 (followed by an asterisk), the last name is placed in column 85, and the date of birth is placed in column 115, all separated by spaces.
My question is twofold:
-
Is there any way to automatically format this without manually using spaces to place the data in the correct columns?
-
I currently have the data in an Excel file, where the initials, last name, and date of birth are in separate columns. Is there an easy way to import this into Notepad++ with the formatting mentioned above?
persondata fictional as exampleI hope someone can help me!
-
-
I currently have the data in an Excel file, where the initials, last name, and date of birth are in separate columns. Is there an easy way to import this into Notepad++ with the formatting mentioned above?
Usually if you select an Excel table, copy it, and paste it into a blank Notepad++ document, you will get the values separated by tab characters (
\tin Notepad++ regex), like so:
becomes
excel table example foo bar 1 TRUE blah 5/14/2023 23333333 FALSE baz bar rkrkrkrkrkk 6/24/2022 3 TRUE nb rkr 3/2/2025These columns are not nicely aligned by default, but if you use the ColumnsPlusPlus plugin and check
Elastic Tabstopsat the top of the plugin’s menu, the columns of the file are aligned visually in a very pleasing way:
I would very strongly urge you not to use a huge number of spaces to separate columns, because you will need to delete spaces between the columns whenever you add a value, and it will be more difficult for a computer to parse than if you just used a tab-separated variables file like what Excel will naturally create.
-
Thank you Mark for your response!
I know it doesn’t make sense to have spaces between the values. But that’s how I’ve been instructed, so I can’t use tabs between the values; it really has to be done with spaces, aligned perfectly in the right columns. It’s frustrating, I know, but apparently, their dept. can’t get it through the system otherwise.
Do you have any suggestions on how to get the values in the correct columns using spaces?
Thanks a lot!
-
This is their official instruction btw:
"Open Notepad++.
Fill in all the information as per the format below.
Start with the initial of the first name (lowercase letter) in column 25 (Col:25), followed by an asterisk.
Then, press the spacebar a few times until you reach column 85.
In Col. 85, enter the last name. Always use the original family name (maiden name) and do not include prefixes such as “de,” “van,” “van der,” etc.
Next, press the spacebar again until you reach column 115. Enter the date of birth in the American format without any hyphens or slashes (yyyymmdd). After the date of birth, enter the gender, V or M in uppercase.!! Please note!
Only spaces may be used between the different data fields; if a tab is used, the file will not be processed.
You can view the distance between the data fields (the columns) in Notepad++ when you look “under the hood.” See the arrow."
-
I’m sorry to hear that your data has to be in this stupid format.
In any case, if you start with an Excel file containing the data and paste it into Notepad++ (or you have a text document with any number of spaces or tabs between the columns), you can convert it to the desired format without using any plugins, using the following sequence of find/replace actions.
First, open up the find/replace form (
Search->Replace...from the main menu or Ctrl+H with default keybindings)Then make sure you have
Search Modeset toRegular expressionandWrap aroundchecked.- Make it so that every column of every row is at least as long as the desired length of that column (24 characters, then 60 characters, then 30 characters, then the fourth column, then the end of the line)
- Find what:
(?-i)^(\d+)[ \t]+(\w+\*)[ \t]+(\w+)[ \t]+(\d+[MV])$ - Replace with:
${1} ${2} ${3} ${4} - Note: the replacement syntax for Notepad++ does not allow quantifiers, so
- Find what:
- Trim trailing space off each column so that it has exactly the required length.
- Find what:
(?-i)^([ \d]{24}) +([\w+* ]{60}) +([\w ]{30}) +(\d+[MV])$ - Replace with:
${1}${2}${3}${4}
- Find what:
This will convert
6871383 Wq* Mhwlvpprxjmto 27162277V 90117972 Jedvhqunglvzwae* Yhcojjl 57550281V 8 Ofgqnzedzyzjavv* Udtcayslobs 80371317M 47656203 Ttglxh* Exmijcyzhrnnbtk 23173031M 1220559 Wln* Rkvdrpiwtqzlkqx 46680349M 4687413550290 Mbddixuwyy* Vaieevsjdvfyhii 26655747V 778842352801393 Vwap* Sgkhkhxkvkz 93499733V 6196544 Ne* Aa 42732984M 883 Rqf* Gizxdjvulzxma 89321185V 6799389068741 Zgxfjwmkudh* Fxt 47185515Minto the desired result
6871383 Wq* Mhwlvpprxjmto 27162277V 90117972 Jedvhqunglvzwae* Yhcojjl 57550281V 8 Ofgqnzedzyzjavv* Udtcayslobs 80371317M 47656203 Ttglxh* Exmijcyzhrnnbtk 23173031M 1220559 Wln* Rkvdrpiwtqzlkqx 46680349M 4687413550290 Mbddixuwyy* Vaieevsjdvfyhii 26655747V 778842352801393 Vwap* Sgkhkhxkvkz 93499733V 6196544 Ne* Aa 42732984M 883 Rqf* Gizxdjvulzxma 89321185V 6799389068741 Zgxfjwmkudh* Fxt 47185515MNote that this regex-replace would fail to transform lines where one of the columns has too many non-space characters. For example, the line
43434 Blah* TheSecondColumnIsTooLongSoItWillNotBeTransformedInTheSecondStep 12345678Mwould be transformed by step 1 above, but not step 2. Because of this, those lines will not be visually aligned with the others, and it should be able to see them when scanning through the file after transformation. - Make it so that every column of every row is at least as long as the desired length of that column (24 characters, then 60 characters, then 30 characters, then the fourth column, then the end of the line)
-
@Milo-v-Hoefen-Wijsard One additional thing is that in the Notepad++'s Settings / Preferences / Language section. Change the
indent sizefrom 4 to 30 and change theIndent usingfrom tab to space.Key in your data using
<tab>initial of the first name<tab><tab>last name<tab>date of birthWith the tab indent of 30 and double tabbing between the first and last name that will drop the data into columns 31, 91, and 121. Now you only need to remove six spaces from columns 25 to 31 using a search replace of
Search:
(?<=^.{24}) {6}
Replace: (nothing) or blank
Note that there is a space between the right parentheses and the{6}.That will shift the data left by six characters from columns 31, 91, and 121 to 25, 85 and 115.
Off topic edit about Excel. If you have the data in Excel already then you could structure the data within Excel and don’t need to do anything in Notepad++. Let’s say, in Excel column A is the first name, B is the last name, and C has the date of birth. Add a new column, let’s call it export, with:
=REPT(" ",24)&LOWER(LEFT(A2,1))&"*"&REPT(" ",58)&LEFT(B2&REPT(" ",30),30)&TEXT(C2,"mm/dd/yyyy")That formula is for row 2 as I assume you have a header row. In summary, I’m padding the cell data out to the column width with spaces and then truncating the result to the column width.
-
@Milo-v-Hoefen-Wijsard said in How to (auto) format text to start at column 25, 85 and 115:
I can’t use tabs between the values; it really has to be done with spaces
Just wanted to mention that you could still use the Columns++ plugin with Elastic tabstops enabled, as Mark Olson mentioned.
First (after installing the plugin and enabling Elastic tabstops), enter the data using tabs to separate. When you are finished, use the Convert tabs to spaces command on the Columns++ menu to replace the tabs with spaces.
Then, you can add spaces as needed to put the columns in the correct places using rectangular selections. Click the first line just to the left of the text that begins the second column. Press Ctrl+Alt+down-arrow to make a zero-width rectangular selection that extends all the way to the end of the file. (That’s a Columns++ shortcut.) Then press the space bar repeatedly until the column indication in the status bar at the bottom shows 25. Do the same for the remaining columns.
-
@Milo-v-Hoefen-Wijsard said in How to (auto) format text to start at column 25, 85 and 115:
I currently have the data in an Excel file, where the initials, last name, and date of birth are in separate columns. Is there an easy way to import this into Notepad++ with the formatting mentioned above?
I have LibreOffice, rather than Excel, installed, but it should be possible to do this in Excel.
Choose Save As….
Select Save as type: Text/CSV.
Check the box Edit filter settings.
Choose a file name and location and click Save.On the next dialog, choose Fixed column width.
The result will have the columns in line, but it won’t be in the correct columns. You can use rectangular selections in Notepad++ to move the columns over to the correct positions. Click at the left edge of the first column you need to move; then scroll down using the scroll wheel or the scroll bar — not the keyboard — and Shift+Alt+Click at the left edge of the same column on the last line. You’ll see a long, flashing line before the column you need to move. Type spaces until the column indicator in the status bar at the bottom shows that you are in column 25. Repeat this for the remaining columns.
-
While it doesn’t do any formatting of data, the Vertical Edge feature may be handy for working with the data described. It seems you have to specify one less than the column you want, but here’s a screenshot of the setup and what it then looks like with some data:
-
Another editing technique that might help; say you have this:
Clearly all of the data in the second column needs to be lined up.
If you create a column caret using Shift+Alt+arrows and make it look like this:
and then press Ctrl+Delete, you’ll get something looking like this:
It’s then a simple matter to press space-bar a few times to get all of the data aligned, and in the correct column:
Note that in the create-column-caret step, the column caret does NOT touch any of the text to be aligned, there is one or more intervening space(s) (this is a very important point).
