Removing only the numbers in a list box

[#1]

Hello!

I have a variable size for an ID in a text (such 152 Costumer1, 15580 Costumer 2).

I need to remove this ID and if I use text function such RIGHT, LEFT, MID.... I just can't remove only the number from the ID, I have no idea how to select only number in the first chars.

Does anyone have an idea how to remove these numbers?

Obs: I have number in some costumers names, so I can't just remove all the number, only the numbers before the first "text char".

Thanks a lot.

[johnw · Accepted solution]

If there's a space between the ID and the rest of the field, perhaps this?

mid(Customer,index(Customer,' ')+1)

[#3]

John,

It worked!

But, i had some new "number".

There's some numbers in the left side of the list box and I don't know now how to remove they too. If I put a order rule I can see that's about 30 items in that situation, but I think that should be something with the data in the SQL.

Thanks for your help.

[#4]

Here's another expression to do it, I also might create a new field in the load script with just the leading number instead of doing it in the list box.

=subfield(Customer,' ',1)

[johnw]

pauloespinoza wrote:There's some numbers in the left side of the list box and I don't know now how to remove they too. If I put a order rule I can see that's about 30 items in that situation, but I think that should be something with the data in the SQL.

If you can't resolve it, try posting a few rows of your "bad" data here and we'll see if we can figure it out.

[#6]

Hey there, i'm posting the screen shot, so if you guys have any idea how to solve that, i'll appreciate.

Thanks a lot!

[#7]

If you can guarantee that your first usable character is between a-z OR A-Z then this might work for you:

MID(Customer,FindOneOf(Customer,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')) AS NewCustomer

[#8]

Yeah!

Now it worked fine and thanks a lot for all of you to help me on that!

Thanks and have a good weekend.