| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Make them work on the Each cell Value
Hello from Steved
in Cell A1 I have 5854, now picking up the first digit which is 5 it will type in "Shore" Please how do I ask the bottom Formulas to do this please =IF(LEFT(A2,1)="1","City","")+=IF(LEFT(A2,1)="2"," Roskill","") as this method leaves me a true or false . =IF(LEFT(A2,1)="1","City","") =IF(LEFT(A2,1)="2","Roskill","") =IF(LEFT(A2,1)="4","Wiri","") =IF(LEFT(A2,1)="5","Shore","") =IF(LEFT(A2,1)="6","Orewa","") =IF(LEFT(A2,1)="7","Swanson","") =IF(LEFT(A2,1)="8","Panmure","") Thankyou. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Make them work on the Each cell Value
Try
=IF(LEFT(A2,1)="1","City","")&IF(LEFT(A2,1)="2","R oskill","")&IF(LEFT(A2,1)="4","Wiri","")&IF(LEFT(A 2,1)="5","Shore","")&IF(LEFT(A2,1)="6","Orewa","") &IF(LEFT(A2,1)="7","Swanson","")&IF(LEFT(A2,1)="8" ,"Panmure","") I have put & between each pair. & concatenates the strings... The formula above joins the results of each IF together which gives you the desired answer _____________________ Click 'Yes' if this helped. "Steved" wrote: Hello from Steved in Cell A1 I have 5854, now picking up the first digit which is 5 it will type in "Shore" Please how do I ask the bottom Formulas to do this please =IF(LEFT(A2,1)="1","City","")+=IF(LEFT(A2,1)="2"," Roskill","") as this method leaves me a true or false . =IF(LEFT(A2,1)="1","City","") =IF(LEFT(A2,1)="2","Roskill","") =IF(LEFT(A2,1)="4","Wiri","") =IF(LEFT(A2,1)="5","Shore","") =IF(LEFT(A2,1)="6","Orewa","") =IF(LEFT(A2,1)="7","Swanson","") =IF(LEFT(A2,1)="8","Panmure","") Thankyou. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Make them work on the Each cell Value
Hi,
Make a table with 1 throught 8 in the first column and the words you want in the second column. Say you do this in D1:E8 then use the formula =LOOKUP(LEFT(A2),D1:E8) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Steved" wrote: Hello from Steved in Cell A1 I have 5854, now picking up the first digit which is 5 it will type in "Shore" Please how do I ask the bottom Formulas to do this please =IF(LEFT(A2,1)="1","City","")+=IF(LEFT(A2,1)="2"," Roskill","") as this method leaves me a true or false . =IF(LEFT(A2,1)="1","City","") =IF(LEFT(A2,1)="2","Roskill","") =IF(LEFT(A2,1)="4","Wiri","") =IF(LEFT(A2,1)="5","Shore","") =IF(LEFT(A2,1)="6","Orewa","") =IF(LEFT(A2,1)="7","Swanson","") =IF(LEFT(A2,1)="8","Panmure","") Thankyou. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Make them work on the Each cell Value
Create a 2 column table like this:
........E........F 1.....1.....City 2.....2.....Roskill 3.....4.....Wiri 4.....5.....Shore 5.....6.....Orewa 6.....7.....Swanson 7.....8.....Panmure Then use a formula like this: A1 = 5845 =IF(COUNTIF(E1:E7,LEFT(A1)),VLOOKUP(--LEFT(A1),E1:F7,2,0),"") I notice you're missing a condition for number 3? -- Biff Microsoft Excel MVP "Steved" wrote in message ... Hello from Steved in Cell A1 I have 5854, now picking up the first digit which is 5 it will type in "Shore" Please how do I ask the bottom Formulas to do this please =IF(LEFT(A2,1)="1","City","")+=IF(LEFT(A2,1)="2"," Roskill","") as this method leaves me a true or false . =IF(LEFT(A2,1)="1","City","") =IF(LEFT(A2,1)="2","Roskill","") =IF(LEFT(A2,1)="4","Wiri","") =IF(LEFT(A2,1)="5","Shore","") =IF(LEFT(A2,1)="6","Orewa","") =IF(LEFT(A2,1)="7","Swanson","") =IF(LEFT(A2,1)="8","Panmure","") Thankyou. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Make them work on the Each cell Value
=LOOKUP(LEFT(A2),D1:E8)
Did you actually try that formula? I suggest you slow down and try to provide accurate solutions rather than just posting as many replies as you can. Quality beats quantity every time. -- Biff Microsoft Excel MVP "Shane Devenshire" wrote in message ... Hi, Make a table with 1 throught 8 in the first column and the words you want in the second column. Say you do this in D1:E8 then use the formula =LOOKUP(LEFT(A2),D1:E8) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Steved" wrote: Hello from Steved in Cell A1 I have 5854, now picking up the first digit which is 5 it will type in "Shore" Please how do I ask the bottom Formulas to do this please =IF(LEFT(A2,1)="1","City","")+=IF(LEFT(A2,1)="2"," Roskill","") as this method leaves me a true or false . =IF(LEFT(A2,1)="1","City","") =IF(LEFT(A2,1)="2","Roskill","") =IF(LEFT(A2,1)="4","Wiri","") =IF(LEFT(A2,1)="5","Shore","") =IF(LEFT(A2,1)="6","Orewa","") =IF(LEFT(A2,1)="7","Swanson","") =IF(LEFT(A2,1)="8","Panmure","") Thankyou. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Make them work on the Each cell Value
Hello T.Valko
Thankyou, There was a 3 "Papakura" but we sold the land, hence moved the business to 4 "Wiri" Thankyou. Steved "T. Valko" wrote: Create a 2 column table like this: ........E........F 1.....1.....City 2.....2.....Roskill 3.....4.....Wiri 4.....5.....Shore 5.....6.....Orewa 6.....7.....Swanson 7.....8.....Panmure Then use a formula like this: A1 = 5845 =IF(COUNTIF(E1:E7,LEFT(A1)),VLOOKUP(--LEFT(A1),E1:F7,2,0),"") I notice you're missing a condition for number 3? -- Biff Microsoft Excel MVP "Steved" wrote in message ... Hello from Steved in Cell A1 I have 5854, now picking up the first digit which is 5 it will type in "Shore" Please how do I ask the bottom Formulas to do this please =IF(LEFT(A2,1)="1","City","")+=IF(LEFT(A2,1)="2"," Roskill","") as this method leaves me a true or false . =IF(LEFT(A2,1)="1","City","") =IF(LEFT(A2,1)="2","Roskill","") =IF(LEFT(A2,1)="4","Wiri","") =IF(LEFT(A2,1)="5","Shore","") =IF(LEFT(A2,1)="6","Orewa","") =IF(LEFT(A2,1)="7","Swanson","") =IF(LEFT(A2,1)="8","Panmure","") Thankyou. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Make them work on the Each cell Value
=VLOOKUP(LEFT(A2)+0,{1,"City";2,"Roskill";4,"Wiri" ;5,"Shore";6,"Orewa";7,"Swanson";8,"Panmure"},2,)
"Steved" wrote: Hello from Steved in Cell A1 I have 5854, now picking up the first digit which is 5 it will type in "Shore" Please how do I ask the bottom Formulas to do this please =IF(LEFT(A2,1)="1","City","")+=IF(LEFT(A2,1)="2"," Roskill","") as this method leaves me a true or false . =IF(LEFT(A2,1)="1","City","") =IF(LEFT(A2,1)="2","Roskill","") =IF(LEFT(A2,1)="4","Wiri","") =IF(LEFT(A2,1)="5","Shore","") =IF(LEFT(A2,1)="6","Orewa","") =IF(LEFT(A2,1)="7","Swanson","") =IF(LEFT(A2,1)="8","Panmure","") Thankyou. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Make them work on the Each cell Value
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Steved" wrote in message ... Hello T.Valko Thankyou, There was a 3 "Papakura" but we sold the land, hence moved the business to 4 "Wiri" Thankyou. Steved "T. Valko" wrote: Create a 2 column table like this: ........E........F 1.....1.....City 2.....2.....Roskill 3.....4.....Wiri 4.....5.....Shore 5.....6.....Orewa 6.....7.....Swanson 7.....8.....Panmure Then use a formula like this: A1 = 5845 =IF(COUNTIF(E1:E7,LEFT(A1)),VLOOKUP(--LEFT(A1),E1:F7,2,0),"") I notice you're missing a condition for number 3? -- Biff Microsoft Excel MVP "Steved" wrote in message ... Hello from Steved in Cell A1 I have 5854, now picking up the first digit which is 5 it will type in "Shore" Please how do I ask the bottom Formulas to do this please =IF(LEFT(A2,1)="1","City","")+=IF(LEFT(A2,1)="2"," Roskill","") as this method leaves me a true or false . =IF(LEFT(A2,1)="1","City","") =IF(LEFT(A2,1)="2","Roskill","") =IF(LEFT(A2,1)="4","Wiri","") =IF(LEFT(A2,1)="5","Shore","") =IF(LEFT(A2,1)="6","Orewa","") =IF(LEFT(A2,1)="7","Swanson","") =IF(LEFT(A2,1)="8","Panmure","") Thankyou. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Make table query will work in datasheet view but will not make tab | Excel Discussion (Misc queries) | |||
| Cant make it work | New Users to Excel | |||
| can't make it work, I need help | Excel Worksheet Functions | |||
| How can i make this work within an IF statement: (41<=Cell<=46) | New Users to Excel | |||
| Spin button in a work sheet - how do I make it work? | Excel Worksheet Functions | |||
Linear Mode
