Formula 1

[Jeffcis]

Hi Tek-Tips,

The value of my customer_name table, are shown below:

Kym Woolford Carpet Choice SN1517
City Carpet Choice SN1702
Dubbo Carpet Choice SN1220
Hirns Carpet Choice Mt Isa SN1906
Hirns Carpet Choice Mt Isa SN1906

The below formula given to me below, drops part of the customer name leaving only the number

stringvar array x := split({Sheet1_.Sold-to party}," ");
stringvar y := trim(x[ubound(x)]);
replace(y,"SN","")

Result:
1517
1702
122
1906
1906

The formula works perfectly BUT if the customer name has ZERO value at the end of the customer name (eg Dubbo Carpet Choice SN1220), the below result is INCORRECT because it ignores the ZERO number.

122

The correct result should be below:

1220

Can anyone assist on how to amend the above formula to include the ZERO digit particularly when it is placed as the last number from the customer name.

Appreciate any help
Jeffcis

 

[IanWaterman]

This is very odd as I have just modified your formula

stringvar array x := split("Dubbo Carpet Choice SN1220"," ");
stringvar y := trim(x[ubound(x)]);
replace(y,"SN","")

When this is executed it returns 1220.

Are you sure field is not being truncated some how in display?

Ian

 

[nobull613]

You could try something along the line of:
Right({customer_name.Name_Field},Len({customer_name.Name_Field})-Instr({customer_name.Name_Field},"SN")-1)

The -1 accounts for a 2 character value in the Instr formula (SN). This should return all of the values to the right of the SN.

 

[Jeffcis]

Hi Ian,

Correct me if I'm mistaken, but i'm not sure if that is going to work because below is HEADER name of a table so you cannot replace it with the customer name itself.

{Sheet1_.Sold-to party}

 

[Jeffcis]

Thanks nobull613, it worked perfect

 

[nobull613]

You might run into an issue with the formula. If the customers name contains an SN like Snow SN999 or something you'll get everything after that ow SN999. You might need the InStrRev function that starts at the end of the string and works back. Something like:

Right({customer_name.Name_Field},Len({customer_name.Name_Field})-InstrRev({customer_name.Name_Field},"SN")-1)

It will start at the end of the number and work back to an SN. You may need to play with the -1 or +1 to make sure you get the right value.