Substitue for the IF Function
 

I'm needing to display the correct area code for the corresponding postcode,
row two is the example i'm needing to display. The postcode is already there,
it the area code i need the formula for.

A B
1 Postcode AreaCode
2 2148 02

The data is
1XXX & 2XXX = 02
3XXX, 7XXX & 8XXX = 03
4XXX & 9XXX = 07
0XXX, 5XXX & 6XXX = 08

The "X" refer to different numbers that may be entered.

Thanks in advance

Substitue for the IF Function
 

Say the data is in D1, then:

=Right(D1,2)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"KimC" wrote in message
...
I'm needing to display the correct area code for the corresponding
postcode,
row two is the example i'm needing to display. The postcode is already
there,
it the area code i need the formula for.

A B
1 Postcode AreaCode
2 2148 02

The data is
1XXX & 2XXX = 02
3XXX, 7XXX & 8XXX = 03
4XXX & 9XXX = 07
0XXX, 5XXX & 6XXX = 08

The "X" refer to different numbers that may be entered.

Thanks in advance

Substitue for the IF Function
 

On Jan 25, 12:06*pm, KimC wrote:
I'm needing to display the correct area code for the corresponding postcode,
row two is the example i'm needing to display. The postcode is already there,
it the area code i need the formula for.

* * * * * *A * * * * * * * B
1 * *Postcode * *AreaCode
2 * * * 2148 * * * * * 02

The data is
1XXX & 2XXX = 02
3XXX, 7XXX & 8XXX = 03
4XXX & 9XXX = 07
0XXX, 5XXX & 6XXX = 08

The "X" refer to different numbers that may be entered.

Thanks in advance

Hi, Best way to do this, and keep it flexible, is via a table and
VLOOKUP

Create following table and name it "code". This can be on another
sheet, preferably.

PC AC
0 08
1 02
1 02
3 03
4 07
5 08
6 08
7 03
8 03
9 07

use following formula in B2: =VLOOKUP(VALUE(LEFT(A2,1)),code,2,0)

This should work.

Cheers,

Harold

Substitue for the IF Function
 

Assuming your data in A2 is text you can put this in B2:
=CHOOSE(LEFT(A2,1)+1,"08","02","02","03","07","08" ,"08","03","03","07","08")

Tyro

"KimC" wrote in message
...
I'm needing to display the correct area code for the corresponding
postcode,
row two is the example i'm needing to display. The postcode is already
there,
it the area code i need the formula for.

A B
1 Postcode AreaCode
2 2148 02

The data is
1XXX & 2XXX = 02
3XXX, 7XXX & 8XXX = 03
4XXX & 9XXX = 07
0XXX, 5XXX & 6XXX = 08

The "X" refer to different numbers that may be entered.

Thanks in advance

Substitue for the IF Function
 

On Jan 25, 12:19*pm, "RagDyer" wrote:
Say the data is in D1, then:

=Right(D1,2)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------"KimC" wrote in message

...



I'm needing to display the correct area code for the corresponding
postcode,
row two is the example i'm needing to display. The postcode is already
there,
it the area code i need the formula for.

* * * * * A * * * * * * * B
1 * *Postcode * *AreaCode
2 * * * 2148 * * * * * 02

The data is
1XXX & 2XXX = 02
3XXX, 7XXX & 8XXX = 03
4XXX & 9XXX = 07
0XXX, 5XXX & 6XXX = 08

The "X" refer to different numbers that may be entered.

Thanks in advance- Hide quoted text -

- Show quoted text -

Best way to do this is using a table and VLOOKUP

Create following table and name it "code". Preferably on another
sheet.

PC AC
0 08
1 02
2 02
3 03
4 07
5 08
6 08
7 03
8 03
9 07

use following formula in B2: =VLOOKUP(VALUE(LEFT(A2,1)),code,2,0)

Best of luck.

Cheers,

Harold

Substitue for the IF Function
 

KimC, if I understand correctly, any 4-digit post code beginning with 1 has
area code 02, any 4-digit post code beginning with 0, 5 or 6 has area code of
08.

If this is correct, you can set up a small table on that sheet or another
one like this:
A B
1 0 8
2 1 2
3 2 2
4 3 3
5 4 7
6 5 8
7 6 8
8 7 3
9 8 3
0 9 7

Assuming that table is on a sheet named Sheet1 it can be referred to as:
'Sheet1'!$A$1:$B$10
in a formula.

So in your example you have cell A2 containing 2148
In B2 you'd put
=VLOOKUP(INT(A2/1000),Sheet1!$A$1:$B$10,2,0)
the INT(A2/1000) reduces the 2148 to just "2" for the lookup. It will
return a 2 from the table and you can format the cell to display a leading
zero by formatting the column (B) as custom and entering 00 as the format.

The same formula will work all down the sheet as you 'fill' it (see help on
Fill Data) on how to do that. The only part of it that will change is the A2
reference. Lets say you have 6115 in A7, then the formula in B7 would be:
=VLOOKUP(INT(A7/1000),Sheet1!$A$1:$B$10,2,0)
and it would return an 8 from the table.

Hope this helps some.

"KimC" wrote:

I'm needing to display the correct area code for the corresponding postcode,
row two is the example i'm needing to display. The postcode is already there,
it the area code i need the formula for.

A B
1 Postcode AreaCode
2 2148 02

The data is
1XXX & 2XXX = 02
3XXX, 7XXX & 8XXX = 03
4XXX & 9XXX = 07
0XXX, 5XXX & 6XXX = 08

The "X" refer to different numbers that may be entered.

Thanks in advance

Substitue for the IF Function
 

Correction formula should be
=CHOOSE(LEFT(A1,1)+1,"08","02","02","03","07","08" ,"08","03","03","07")

"Tyro" wrote in message
...
Assuming your data in A2 is text you can put this in B2:
=CHOOSE(LEFT(A2,1)+1,"08","02","02","03","07","08" ,"08","03","03","07","08")

Tyro

"KimC" wrote in message
...
I'm needing to display the correct area code for the corresponding
postcode,
row two is the example i'm needing to display. The postcode is already
there,
it the area code i need the formula for.

A B
1 Postcode AreaCode
2 2148 02

The data is
1XXX & 2XXX = 02
3XXX, 7XXX & 8XXX = 03
4XXX & 9XXX = 07
0XXX, 5XXX & 6XXX = 08

The "X" refer to different numbers that may be entered.

Thanks in advance