Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am importing a string from an excel file and i need to clean up one of the colums and make it two columns.
The first thing i need to do was get rid of the first two charcaters whihc was no big deal.
The next part is where i am having the issue i am trying to pull out a chunk of the string to create another field.
My issue is the chunk of data for 90% of the records is the last 10 bytes of the string so thats pretty simply but for the other 10% of the records the chunk is in the first 9 bytes of the string.
ZZXXXXXXXXX XXXXXXXXXXX XXXXX XXXXXXXXX XXXXXX XXXXX 9999999999
ZZ999999999XXXXXXXXX XXXXXXXXXXX XXXXX XXXXXXXXX XXXXXX XXXXX
So to get rid of the first two bytes i just did this which worked fine to get rid of the two bytes i dont want.
right(trim(@2),len(trim(@2))-2) as String1
but to pull out the data for the new field I was simply doing a right function but i need to to pull either the right most 10 characters or the left most 9 characters depending on where the number happens to be and how long it is
right(trim(@2),10) as String2
Hi,
If the required data is only number then you can try this.
Keepchar(Field,'1234567890') as New_Field.
This will remove every other character then numbers.
Hope this will help.
Regards,
Kaushik Solanki
I wish I was that lucky the field has product descriptions in it along with which has things like size and pack size.
This is kind of what i came up with to do what i was looking for.
IF (isnum(left(PURGECHAR(PurgeChar(@2,'"'),'ÿ+'),11)),left(PURGECHAR(PurgeChar(@2,'"'),'ÿ+'),11),RIGHT(PURGECHAR(PurgeChar(@2,'"'),'ÿ+'),10) )