Take out all non numeric characters out of a text string. 2

[gabe17]

Hello,

I am trying to take a database of fax numbers and turn it into ##########@myfax.com so I can batch email the invoices to myfax.com to fax out.

the issue is some fax numbers are formatted ###-###-#### or (###) ###-####, and some ##########@myfax.com already.

So far I've got this:


Replace ({ARcus.TEXTPHON2},("-")(" ")(")")("("),"") & "@myfax.com"

Does someone know how to search multiple replacement values?
I've tried ("-") and (" ")... but it asks for a boolean, and ("-")+(" ") isn't exactly what I want either.

Also, if someone has a better way to only return numeric characters, there's probably an easier way to do it.

Thanks for any help you can provide.

Gabe

 

[fisheromacse]

you will need to nest your replace function. Something like this:

replace(replace(replace({table.field},"-",""),"'",""),"*","")


From LBass here:

http://www.tek-tips.com/viewthread.cfm?qid=1623604&page=1

 

[briangriffin]

If there is any chance that the data entry person typed in something unexpected - like # instead of * - then this might work better - it strips out anything that isn't a number without having to specify what those characters might be:

whileprintingrecords;
stringvar x;
numbervar y;

for y := 1 to len({table.field})

do

(if isnumeric({table.field}[y])
then x := x + {table.field}[y]
else x);

x

HTH

 

[gabe17]

Thanks to both of you for the very quick response!

fisheromacse, that worked great, but briangriffin, this is exactly what I was looking for, just in case there are some characters I was missing from my replace formula.


Thank you very much for taking the time to write back.

Gabe

 

[gabe17]

I noticed my field added up all fax numbers together if I had a batch of invoices, so I've added the following line to briangriffin's formula to take only the data from each page in case someone else needs it.

whileprintingrecords;
If Not InRepeatedGroupHeader then stringvar x:="";
numbervar y;

for y := 1 to len({ARCUS.TEXTPHON2})

do

(if isnumeric({ARCUS.TEXTPHON2}[y])
then x := x + {ARCUS.TEXTPHON2}[y]
else x);

x

& "@myfax.com

 

[gabe17]

Hi,

Sorry me again.

Some of our clients prefer to get sent email instead of a fax.

I need the above formula to not do anything to the value if there's an "@" in the string already.

So basically if the value is something@email.com it will just leave something@email.com, but if it says 433-553-3533 it will change it to 4335533533@myfax.com

Any help would be greatly appreciated.

Thanks a lot,

Gabe

 

[lbass]

Add a clause to your formula:

if isnumeric({ARCUS.TEXTPHON2}[y]) and
instr({ARCUS.TEXTPHON2},"@") = 0 then //etc.

-LB

 

[gabe17]

Hi lbass,

So it may just be where I put this in, but it's not doing the required action. If there's an email address instead of a fax number. It will take out all letters and there will only be @myfax.com in the output.

This is the formula currently:

whileprintingrecords;
If Not InRepeatedGroupHeader then stringvar x:="";
numbervar y;

for y := 1 to len({ARCUS.TEXTPHON2})

do

if isnumeric({ARCUS.TEXTPHON2}[y]) and
instr({ARCUS.TEXTPHON2},"@") = 0 then //

(if isnumeric({ARCUS.TEXTPHON2}[y])
then x := x + {ARCUS.TEXTPHON2}[y]
else x);

x

& "@myfax.com"



-- Thanks for any help in fixing this.

Gabe

 

[lbass]

Try this instead:

whileprintingrecords;
stringvar z := {ARCUS.TEXTPHON2};
stringvar x := "";
numbervar i;
numbervar cnt := 0;
for i := 1 to len(z) do (
if isnumeric(z) then (
cnt := cnt + 1;
x := x + z
));
if cnt = 10 then
x := x + "@myfax.com"
else
x := "Error";
x

You should be able to copy this right into your formula and use it as is (along as you remove other references to variables of the same name from your report).

-LB

 

[gabe17]

Hi LBass,

Thanks again for all your help with this, greatly appreciated.

Okay your above formula works if it's a number, but not if it's an email address, it gives "error" instead of the email address.

Also, it seems to give an error if the fax number starts with a 1 (11 digits).

Thanks again LB,

Gabe

 

[lbass]

Okay, I hadn't reread the entire thread and I thought you wanted to replace all e-mail addresses with "@myfax.com".

whileprintingrecords;
stringvar z := {ARCUS.TEXTPHON2};
stringvar x := "";
numbervar i;
numbervar cnt := 0;
for i := 1 to len(z) do (
if isnumeric(z) and
instr(z,"@") = 0 then (
cnt := cnt + 1;
x := x + z
));
if instr(z,"@") <> 0 then
x := z else
if cnt in [10,11] then
x := x + "@myfax.com"
else
x := "Error";
x

 

[gabe17]

Worked like a charm!

I can't begin to thank you lbass. You're a lifesaver.

Cheers,

Gabe