Split string using regex

[kbsocgen]

Hello,

I'm new to regex and would greatly appreciate some help with splitting below text string into two columns  - Short Name and Full Name. 

Input String

Third Party.... ASI LTD Asstton Sealuck I Limited
Third Party.... ASII LTD Asstton Sealuck II Limited
Third Party.... ASIV LTD Asstton Sealuck IV Limited
Third Party.... AZLAGOS AVERAGE ZONALTAG LAGOS
Third Party.... ABCD FOOTY ABCD FOOTIE Manchesta Industria
Third Party.... LALEASMARC LA Leassee (March) Limited

Expected result

Short Name	Full Name
ASI LTD	Asstton Sealuck I Limited
ASII LTD	Asstton Sealuck II Limited
ASIV LTD	Asstton Sealuck IV Limited
AZLAGOS	AVERAGE ZONALTAG LAGOS
ABCD FOOTY	ABCD FOOTIE Manchesta Industria
LALEASMARC	LA Leassee (March) Limited

Thank you

[Qiu]

@kbsocgen 
I really dont see a pattern here... 😁
Do you have any logic for the parsing?

[flying008]

Hi, @kbsocgen 

There is a so poor way for you .

1- Parse Expression:

^.+\.{4}\s([A-Z])([A-Z]+\s*[A-Z]+)(?=\s\1)\s(.*)$

2- Gif.

@Qiu 

******

Dear, the [Short Name] is the abbreviation of company name, and the [Long Name] is the full name.

[edittmer]

If there is a regex logic this site helps with dynamically showing you the effect of expressions: https://regexr.com/

[kbsocgen]

Hi @flying008 

I tested your parse expression but it only works on two records and not all (screenshot attached). Please can you check?

Thanks

[flying008 · Accepted solution]

Hi, @kbsocgen 

The reason is that the data I use is the table data you posted above, and you are using the data in the Inputfile.txt file. The two data sources are different, because the number of spaces in the middle of the string is not the same, thus your result is different from my screenshot.

******

If input the data of inputfile.txt, you can try use below expression,  then fix the row-6 puzzle(move the 'LA' to full name) with formula tool.

^.+\.{4}\s([A-Z])([A-Z]+\s*[A-Z]+)(?=\s+\1)\s+(.*)$

If you get the desired result, please give me a solution, thanks!  😁

[kbsocgen]

Hi @flying008 - the expression works with the input file. Thank you

[flying008]

Hi, @kbsocgen 

Please mark this as the solution if it answers your question, it will help others to find solutions quicker.