Hello, @somethingmad, @peterjones and All,
@somethingMad, here is an other alternative to the @peterjones’s solution. So, given this INPUT text :
lori.figaro@gmail.com|5.69272238158E11|4547490.0|#11811391_PRP|LORI|FIGARO|75.85|0.0|75.85|0.00|75.85|2622 AVALON FOREST CT||SPRING|Shipping|United States|US|LORI|FIGARO|LORI FIGARO|7132049425|Texas|TX|77386-2972|30.14205889999999|-95.40070209999999||2018-09-15 19:15:52.000|1537064180000|2018-09-01
stcstout@ruraltel.net|5.6932954939E11|4547618.0|#11811461|Tyna| Stoutimore |31.9|0.0|31.9|0.00|39.85|11 SW 2ND ST||PHILLIPSBURG|Shipping|United States|US|Tyna| Stoutimore |Tyna Stoutimore |7855432781|Kansas|KS|67661-2609|39.7476562|-99.32758539999999||2018-09-15 22:53:47.000|1537077256000|2018-09-01
annijchaz@msn.com|5.69279643726E11|4547508.0|#11811400_PRP|Annette|Chavez|128.65|0.0|128.65|0.00|163.65|5812 TRAIL CT NW||ALBUQUERQUE|Shipping|United States|US|Annette|Chavez|Annette Chavez|5053071755|New Mexico|NM|87107|35.1414399|-106.6457214||2018-09-15 19:40:47.000|1537065705000|2018-09-01
with the regex S/R below :
(?xi) ^ # SEARCH regex in 31 lines
( [\w.@]* ) \| # Field 01
( [\dE.]* ) \| # Field 02
( [\d.]* ) \| # Field 03
( \# \w* ) \| # Field 04
( [\w ]* ) \| # Field 05
( [\w ]* ) \| # Field 06
( [\d.]* ) \| # Field 07
( [\d.]* ) \| # Field 08
( [\d.]* ) \| # Field 09
( [\d.]* ) \| # Field 10
( [\d.]* ) \| # Field 11
( [\w ]* ) \| # Field 12
( [\w.: -]* ) \| # Field 13 EMPTY
( \w* ) \| # Field 14
( \w* ) \| # Field 15
( [\w ]* ) \| # Field 16
( \w* ) \| # Field 17
( [\w ]* ) \| # Field 18
( [\w ]* ) \| # Field 19
( [\w ]* ) \| # Field 20
( \d* ) \| # Field 21
( [\w ]* ) \| # Field 22
( \w* ) \| # Field 23
( [\d-]* ) \| # Field 24
( [\d.]* ) \| # Field 25
( [\d.-]* ) \| # Field 26
( [\w.: -]* ) \| # Field 27 EMPTY
( [\d.: -]* ) \| # Field 28
( \d+ ) \| # Field 29
( [\d-]* ) $ # Field 30
REPLACE $1|$5|$6|$12|$14|$16|$21|$23|$29
We get this OUTPUT text :
lori.figaro@gmail.com|LORI|FIGARO|2622 AVALON FOREST CT|SPRING|United States|7132049425|TX|1537064180000
stcstout@ruraltel.net|Tyna| Stoutimore |11 SW 2ND ST|PHILLIPSBURG|United States|7855432781|KS|1537077256000
annijchaz@msn.com|Annette|Chavez|5812 TRAIL CT NW|ALBUQUERQUE|United States|5053071755|NM|1537065705000
Practically :
Close the Find/Replace/Mark dialog if already opened
Select all the search regex, between (?x) and Field 30 included
Open the Replace dialog ( Ctrl + H )
=> The Find what : zone is immediately populated
Select all the replacement regex and paste it in the Replace with : zone
Uncheck all box options
Check the Wrap around option
Select the Regular expression search mode
Click once on the Replace All button
Of course, as I’m French, just verify if the appropriate columns are written after the global replacement !! Anyway, it shouldn’t be difficult to select the right columns, with the given search regex ;-)
Best Regards,
guy038