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.

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.

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.

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.

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.

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.

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.

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.