Hi Aye,

Please upload an Excel file containing a sample of your data with enough examples that cover every scenario, and show the desired result of the column split. From there we will be able to understand what you're trying to achieve.

Mynda

Thanks, Mynda,

Please see attached excel file and my expected result.

Thank you.

Regards,

Aye

Before I spend a lot of time on this, I have to ask, where does the data come from?  Can't you do something at the source of the data to prevent the invoice number and company name from being joined?

They key to this kind of problem is having the data in a consistent format and this isn't.  The invoice numbers take several different forms.

Regards

Phil

Hi Aye

You could try this but like Philip said above, it is best if the problem could be resolved at the source.

Sunny

Many thanks, Sunny and Phil.

I will try to know the system as well.

Much appreciated for your kind help.

Regards,

Aye

Nice work Sunny.

Aye - attached is a VBA solution that uses RegEx in a UDF.  You'll need to set a reference to the 'Microsoft VBScript Regular Expressions 5.5' from the VBA editor.

As with Sunny's workbook, this doesn't work with the entry Programmed Health Professional #750246 because it's a different format to all the others.  This is what I meant by sorting out the formatting of the data at source.

Regards

Phil