Removing text from a number

[JimmyEdwards]

I'm trying to pick up a mobile number from a database, but the data is rather messy.

A lot of the number's have hash characters (#) at the end, and a lot also have a description of the number. I have worked around the first bit, but the text is causing me problems.

This is my formula:-

------------------------------------------------------------
StringVar Mobile :=

if ({Interface_TextMsg.Tel_No_To} startswith "07"
or {Interface_TextMsg.Tel_No_To} startswith "14107"
or {Interface_TextMsg.Tel_No_To} startswith "0 7"
or {Interface_TextMsg.Tel_No_To} startswith "128007"
or {Interface_TextMsg.Tel_No_To} startswith "147007")
then {Interface_TextMsg.Tel_No_To};

Mobile:= Replace (Mobile," ", "");
Mobile:= Replace (Mobile,"#", "");
If isnumeric(mobile) = false then Mobile:=""

Mobile:= "/DDD " & Mobile;
Mobile:= Replace (Mobile,"14107" , "07");
Mobile:= Replace (Mobile,"0 7", "07");
Mobile:= Replace (Mobile,"128007", "07");
Mobile:= Replace (Mobile,"147007", "07");

Mobile
------------------------------------------------------------

With a value of "07876 578236# Brother"

The reult I would like is "/DDD 07876578236"

Because of this line:-

If isnumeric(mobile) = false then Mobile:=""

I am getting "/DDD". I would like a way to remove the "Brother" bit of the valuem, but this text could be anything.

Hoep this makes sense, and thanks for your help.

 

[synapsevampire]

Simplify removing extra characters with:

whileprintingrecords;
stringvar in:={Interface_TextMsg.Tel_No_To};
Stringvar out:="";
numbervar x;
For x := 1 to len(trim(in)) do(
if isnumeric(mid(in,x,1)) then
out:=out+mid(in,x,1)
);
Out:= Replace (Out,"14107" , "07");
Out:= Replace (Out,"128007", "07");
Out:= Replace (Out,"147007", "07");
Out:= "/DDD " & Out;
Out

The only pitfall I still see is that it's possible you might have a valid string of numbers that match the replce statements that shouldn't be replaced, so the replaces might be better served as:

if left(out,6) in ["128007","147007"] then
out:= "07" & mid(out,7);
if left(out,5) = "14107" then
out:= "07" & mid(out,6);
out

-k

 

[JimmyEdwards]

Thanks Synapse.

I've actually solved the problem using the Val() function, which I wasn't previously aware of. This converts a string to a number removing any non numeric characters. I then convert it back to text and add the 0 back on (it's removed on conversion to a number).

This section of my formula now reads like this:-

------------------------------------------------------------
Mobile:= Replace (Mobile,"14107" , "07");
Mobile:= Replace (Mobile,"0 7", "07");
Mobile:= Replace (Mobile,"128007", "07");
Mobile:= Replace (Mobile,"147007", "07");
Mobile:= Replace (Mobile," ", "");
Mobile:= Replace (Mobile,"#", "");
Mobile:= totext(val(Mobile),0,"");
Mobile:= "0" & Mobile;

Mobile:= "/DDD " & Mobile;

Mobile
------------------------------------------------------------

I hadn't considered a valid use of the prefixes I've removed within the middle of the number, so I'll now adapt the code to work around that as you suggest.

Thanks for your help.

James

 

[synapsevampire]

VAL does NOT convert a string to a number removing any non numeric characters. What it does is strip off trailing characters from a field, so if they have placed a character anyhere within or before the numeric it won't work.

To be safe, use my formula as it will be accurate, yours MIGHT be, depending upon the data.

-k

 

[synapsevampire]

Also you can omit the 0 7 replace when you use mine as it has already fixed that.

-k