I have a table 20 columns wide and millions of rows long.

The information is public and voluntarily made public and disseminated publicly. I am an attorney, and I do not believe there can be any real concern about showing this data.

In this filtered set of rows, the 20thcolumn is blank. In this filtered set of rows all the information in column 19 belongs in column 20, and column 19 (in these rows) should be blank.

The illustration shows that the line feed characters do not extend into the 20th column. The green shows the lines ending in the 19th column.

I want to insert one separator, the "|", to the left of the 19th column (of these filtered rows).

I used a particular filter to find the rows containing the data in the 19th column that belongs in the 20th column. The filter was [^(One Manager OR More Than One Manager OR All LLC Member(s))] This finds all the rows that do not have the magic words that would belong in column 19.

What was left were the rows that had data or just a [CR][LF] in column 19 that really belongs in column 20.

I think I need to search for the end of column 18 of each row (filtered) and insert a pipe (the separator).

I thought I could search for the separator and replace it with two separators, but I never figured how to do that correctly, and I would need to do so in a column.

PICTUREThe rows that are filtered have nothing in the farthest right column, and I tried to search for the separator "|" within the 18th column, which does not find any

ENTITY_NAME|ENTITY_NUM|INITIAL_FILING_DATE|JURISDICTION|ENTITY_STATUS|STANDING_SOS|ENTITY_TYPE|FILING_TYPE|FOREIGN_NAME|STANDING_FTB|STANDING_VCFCF|STANDING_AGENT|SUSPENSION_DATE|LAST_SI_FILE_NUMBER|LAST_SI_FILE_DATE|PRINCIPAL_ADDRESS|MAILING_ADDRESS|PRINCIPAL_ADDRESS_IN_CA|LLC_MANAGEMENT_STRUCTURE|TYPE_OF_BUSINESS
|3553873|02/25/2013|ILLINOIS|Active|Good|Stock Corporation - Out of State - Stock|Foreign|"HARRIS ELEVATOR"|Good|Good|Good||LBA285834|Mar  2 2022 12:00AM|521 NORTH ILLINOIS STREET ATWOOD IL  61913|521 NORTH ILLINOIS STREET ATWOOD IL  61913||ELEVATOR MANUFACTURING
|2680629|09/27/2004|NEVADA|Terminated|Good|Stock Corporation - Out of State - Stock|Foreign|"RS47"|Good|Good|Good|2008-12-26 00:00:00.000|LBA55830|Aug  1 2007 12:00AM|8383 WILSHIRE BLVD #116 BEVERLY HILLS CA  90211|8383 WILSHIRE BLVD #116 BEVERLY HILLS CA  90211|8383 WILSHIRE BLVD #116 BEVERLY HILLS CA  90211|PERSONAL CARE PRODUCTS
#1 CASH 4 JEWELS, INC.|3326312|10/26/2010|CALIFORNIA|Terminated|Good|Stock Corporation - CA - General|Domestic||Good|Good|Good|2013-04-02 00:00:00.000|LBA413061|May  1 2013 12:00AM|11069 WARNER AVE FOUNTAIN VALLEY CA  92708|11069 WARNER AVE FOUNTAIN VALLEY CA  92708|11069 WARNER AVE FOUNTAIN VALLEY CA  92708|JEWELRY BUYING

CodePudding user response：

You can turn off the Cell Selection Mode to reveal the pipe delimiters. Alternatively, while in the Cell Selection mode, you can insert the pipe delimiters at the end of selected column:

1. While being filtered, select the 18th column (where you need to insert a pipe at the end of the column) by clicking the column heading.
2. Press Ctrl H to display the Replace dialog box.
3. Click the Advanced button and set the '^' and '$' can Match Beginning and End of the Selection check box. Click OK.
4. Enter Find: $ Replace with: | Make sure In the Selection Only and Regular Expressions options are set.
5. Click the Replace All button.