Formula to streamline addresses

[Abner2]

I'm trying to streamline some addresses. There is really only one major issue I have. A business address is entered one of two ways:

123 Main St @ (Supermarket Name)

or

(Supermarket Name) @ 123 Main St.

This ruins my statistics if I want to see which address has the most activity because the same address is counted as two seperate addresses since they are entered two different ways. Is there a simple formula that will fix this error or would I have to make a formula for each individual address? Thanx

 

[Turkbear]

Hi,
The best approach would be to fix the data..
You could probably test for a number that starts the address and assume , then, that it is in form 1 -
By using various instr and substr functions, you could reformat it.( same technoque to ID form 2 types)

Lots of work.

 

[synapsevampire]

There are a few ways to go here as Turk points out, but consider rnning it through a list service to standardize the address and move the supermarket name into it's own field.

It's much easier to construct Raleys @ 123 Main than to deconstruct it.

Here's a quick formula you might try:

if isnumeric(mid({table.address},instr({table.address},"@&quot+1,1)) then
mid({table.address},instr({table.address},"@&quot+1)+" @ "+
left({table.address},instr({table.address},"@&quot-1) else
{table.address}

I don't have Crystal here but the theory is OK.

-k