RegEx to parse data of variable lengths

[HBarlow]

I have source data which is made up of a company code, company name, date billed, and the amount owed all in one cell in Excel. I need to parse the data into four columns that represent the four elements. I’m having some trouble using the RegEx tool to parse the data, as the company name and the amount owed are variable lengths. The company code will always be 5 digits and the date billed will always be in the same format (mm/dd/yyyy). Any help is greatly appreciated.

Source data:

Company information

52073 Binford Tools, Inc. 12/07/2022 ($183.54)

89682 Acme Corporation 10/27/2022 ($1,255.50)

80364 Globex Corporation (Springfield) 11/22/2022 ($546.90)

50074 Gekko & Co of New York Inc 08/30/2023 ($240.75)

83140 Genco Pura Olive Oil Company 01/01/2023 ($605.23)

Desired output:

Company Code	Company Name	Date Billed	Amount
52073	Binford Tools, Inc.	12/07/2022	($183.54)
89682	Acme Corporation	10/27/2022	($1,255.50)
80364	Globex Corporation (Springfield)	11/22/2022	($546.90)
50074	Gekko & Co of New York Inc	08/30/2023	($240.75)
83140	Genco Pura Olive Oil Company	01/01/2023	($605.23)

[danilang · Accepted solution]

hi @HBarlow 

Use (\d{5})\s(.*?)\s(\d{2}\/\d{2}\/\d{4})\s(.*) as your regex string.  This will break your input into 4 fields.

Dan

[caltang · Accepted solution]

Alternatively, you can also use:

^(\d+) (.*?) (\d{2}/\d{2}/\d{4}) (\(.*\))$

[Qiu · Accepted solution]

@HBarlow 
A completly unnessary complicated one. 
Tried to avoid Regex but failed and landed with a simpler Regex though.

[flying008 · Accepted solution]

Hi, @HBarlow 

^(\d+)\s([^\d]+)\s([\/\d]+)\s(.+)$

[HBarlow]

Thank you, everyone, for your solutions! All of these worked great! I'm new at using RegEx and still learning the nuances.