substring formula

[#1]

Hi Team

From Address field, i have to extract the pincode written in it. But there is no specific position where it can start in the Address field.

What my idea is start from position 1 till the end of the Address value, take 6 char everytime and check whether it is Num.

i am writing VB macro for it.

Is there any QV function to do so?

Also pls suggest if there is any other way to do the same.

Its urgent.

Thanks.

Arun Mittal

[#2]

Hi Arun,

Sql equilvalent substring function in qv is mid

for e.g string

address1 = "Mumbai - 400092"

mid(address1,1,6) will return '400092'

mid(address2,10,6) will return '400092'

You can use the left function also if the PIN number starts from position 1 .

for e.g.

left(address1,6) will also return '400092'

Best regards,

tapas

[#3]

Hi,

Can you post the sample of your data.?

- Sridhar

[#4]

Thank for the reply tapas.

But, as i clearly mentioned in the post that position for Pincode value is not fixed in my Address Field.

[#5]

HDFC Bank Ltd., Kolkatta - 700 001, WB

HDFC Bank Ltd., 700 001 Kolkatta WB

HDFC Bank Ltd., Kolkatta, WB - 700 001

As u can see, in above three records, position of pincode is different.

[#6]

Hi,

check this attached application.

- Sridhar

[#7]

Thanks a lot for the reply. But what happened if the data is like below: 1st Floor, K.B.Road, Near National Insurance Co, Jorhat-785001

2nd Floor, President Tower, Thana Road, Jorhat, Assam - 785001

Deepali Medical Complex, 2 nd floor, A.T.Road, Jorhat - 785001

Ground Floor,Shop No1, Laxmi Narayan Avenue, G.T.Road,Murgasol,Asansol,Dist- Burdwan,West Bengal-713303.

if string contains some other numeric chars also, result will be concatenation of all those like for first record it will be - 1785001...

[#8]

Hi Amit,

check this attached application.

- Sridhar

[#9]

Thank you very much.

Let me check whether this will be applicable for my scenerio. Will catch u if any other required

🙂