Log In
Register
Home
December 22, 2018
Offline
Hi Forum,
I have attached data and (legal Number) column will require a split.
as you can see they are not in order and text to column is not that helpful.
any tips to get them sorted.
Regards,
Aye
July 16, 2010
Offline
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
December 22, 2018
Offline
Thanks, Mynda,
Please see attached excel file and my expected result.
Thank you.
Regards,
Aye
October 5, 2010
Offline
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
VIP
Trusted Members
June 25, 2016
Offline
Hi Aye
You could try this but like Philip said above, it is best if the problem could be resolved at the source.
Sunny
December 22, 2018
Offline
Many thanks, Sunny and Phil.
I will try to know the system as well.
Much appreciated for your kind help.
Regards,
Aye
October 5, 2010
Offline
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
1 Guest(s)
