4 Replies Latest reply on May 15, 2013 12:57 PM by Alex Kerin

# How to remove numbers at the end of the string

Hi

I have this dimension which has values like

abcd

edh11

jhdhjk543

as221

I just want to retrieve the alphabetic values from it which should be

abcd

edh

jhdhjk

as

Thanks

• ###### 1. Re: How to remove numbers at the end of the string

I would guess you are going to have to use a nested if

left([Values],

if not isnull(int(mid([Values],1,1))) then 1

elseif not isnull(int(mid([Values],2,1))) then 2

elseif not isnull(int(mid([Values],3,1))) then 3

elseif not isnull(int(mid([Values],4,1))) then 4

elseif not isnull(int(mid([Values],5,1))) then 5

elseif not isnull(int(mid([Values],6,1))) then 6

elseif not isnull(int(mid([Values],7,1))) then 7

elseif not isnull(int(mid([Values],8,1))) then 8

elseif not isnull(int(mid([Values],9,1))) then 9

elseif not isnull(int(mid([Values],10,1))) then 10

else len([Values])+1

end

-1)

works, but I actually had to extract the data as the query was too complex for JET.

1 of 1 people found this helpful
• ###### 2. Re: How to remove numbers at the end of the string

Hi Alex

This solution is somewhat close, but actually if the length of the string keeps on changing then i have to add more elseif statements in the loop.

I just want to avoid that, i need something which is a one time calculated field which is not dependant on the length of the string.

Thanks

Sid

• ###### 3. Re: How to remove numbers at the end of the string

Presumably you have a max string length - that's how far you need to go.

As we do not have access to real looping, I don't think you have any other choice.

EDIT: There is one other choice actually - you could nest a bunch of Replace([Values],"1","") within each other - this is length independent, but you would need to check for every number

• ###### 4. Re: How to remove numbers at the end of the string

Like this:

Replace(Replace(Replace([Values],"3",""),"2",""),"1","") all the way from 0 to 9

EDIT: here:

Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace([Values],"0",""),"1",""),"2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9","")

1 of 1 people found this helpful