Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help on substitue value, by excel function | Excel Worksheet Functions | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Hex2Dec substitue? | Excel Discussion (Misc queries) | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions |