0

I have a text file and using Notepad++. I have input lines as below (with new line and no set pattern) and want to extract the number before T and output the numbers as pipe delimited.

Input

(1745817T OR 1745818T OR 1745820T OR 1745819T) AND NOT (1786717T)
(3406379T OR 3410183T OR 3414397T OR 3272288T OR 3348134T OR 3379438T OR 3348136T OR 3420508T OR 3420503T OR 3420511T OR 3420504T OR 3420505T OR 3420507T OR 3420512T)

Output

1745817|1745818|1745820|1745819|1786717
3406379|3410183|3414397|3272288|3348134|3379438|3348136|3420508|3420503|3420511|3420504|3420505|3420507|3420512

Please help with how to extract this string from the lines. Regards, Ashish

  • Have you looked into regular expressions? – Patrick R. Jun 4 '18 at 10:44
  • 1
    Please explain how this question is related to microsoft-excel or if not really related, remove the microsoft-excel tag – Tom Brunberg Jun 4 '18 at 11:01
  • In your OUTPUT still I can find Numbers ends with T, what about those ,,, write me whether you have NUMBER ENDS WITH T only or mixed also? – Rajesh S Jun 4 '18 at 11:08
  • Are these number are in one cell or in different Cells? – Rajesh S Jun 4 '18 at 11:32
  • my bad, the output is without "T"-the specific character. And these lines are basically in an excel file. I thought copying them to notepad++ can ease up the output. I am soliciting ways either in excel or in notepad++. Sample here is 2 line text file, wants to convert it into 2 lines of output. – AshishETL Jun 4 '18 at 15:33
3

Edit, according to request change:

  • Ctrl+H
  • Find what: ([()])|T[^\d\r\n]+(\R)?
  • Replace with: (?1:(?2$2:|))
  • check Wrap around
  • check Regular expression
  • Replace all

Explanation:

    ([()])      : group 1, open or close parenthesis
  |             : OR
    T           : the letter T
    [^\d\r\n]+  : 1 or more any character that is not a digit or linebreak
    (\R)?       : group 2, a line break, optional

Replacement:

(?1:            : conditionnal replacement, 
                    if group 1 exists replace with nothing
  (?2$2:|)      : conditionnal replacement, 
                    if group 2 exists let it at same place
                    else replace with pipe |
)

Result for given example:

1745817|1745818|1745820|1745819|1786717
3406379|3410183|3414397|3272288|3348134|3379438|3348136|3420508|3420503|3420511|3420504|3420505|3420507|3420512

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.