Search a string to pull out data.

[#1]

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

[kaushiknsolanki]

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

[#3]

I wish I was that lucky the field has product descriptions in it along with which has things like size and pack size.

[#4]

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) )