Cut out the first number from a field, removing any text before it

[datanibbler]

Hi,

I have to cut out the first seven digits of a field - that is an item_number - but there might or might not be some text before it (like "SN" or anything).

=> How can I find out what is the starting_position of anything numeric and cut out seven digits from that position?

Thanks a lot!

Best regards,

DataNibbler

[Anonymous]

What is the source of this data?

This might be more easily accomplished during load.

[its_anandrjs]

Provide any sample data please.

Regards,

Anand

[rustyfishbones]

can you try

LEFT(PURGECHAR(item_number,'abcdefghijklmnopqrstuvwxyz'),7)

[rustyfishbones · Accepted solution]

You can use this

LEFT(PURGECHAR(item_number,'abcdefghijklmnopqrstuvwxyz'),7)

in the script or as a List Box Expression

[datanibbler]

Hi,

the source is an Excel file.

The problem I have is that the people populating that Excel_file do not adhere to any standard - there is always an item_number and a nr_of_items in one field, but there may be more - optional text_strings and stuff.

One step would be finding out if there is any text before the first number - that is not relevant to me and I'd want to remove it and just split the item_nr (7 digits) from the position of the first digit.

In this instance, the field looks like this

SN 8638294 / 4 Stück / S80140915

Thanks a lot!

[rustyfishbones]

If this is the case

SN 8638294 / 4 Stück / S80140915

then change the code to below

LEFT(PURGECHAR(item_number,'abcdefghijklmnopqrstuvwxyz '),7)

[datanibbler]

Hi Alan,

I guess I will do that.

Thanks a lot!

Best regards,

DataNibbler

P.Sl.: ... Only I will rather use Keepchar() - I don't know what ideas those people might have had about putting anything in front of the number - but I'm sure I want only numeric digits, so that is a lot less typing and more robust, too.