1=City, 2=Roskill
 

Hello from Steved

I would like to please use only the first number as a identifier.

for example 1234, 1543, 1789, 1673 in this case the 1 will = City
for example 2234, 2543, 2789, 2673 in this case the 2 will = Roskill

Thankyou.

Sorry I posted twice by mistake.

"Steved" wrote:

Hello from Steved

I would like to please use only the first number as a identifier.

for example 1234, 1543, 1789, 1673 in this case the 1 will = City
for example 2234, 2543, 2789, 2673 in this case the 2 will = Roskill

Thankyou.

=IF(LEFT(A1)="1","City",IF(LEFT(A1)="2","Roskill", ""))

Regards
Rowan

"Steved" wrote:

Hello from Steved

I would like to please use only the first number as a identifier.

for example 1234, 1543, 1789, 1673 in this case the 1 will = City
for example 2234, 2543, 2789, 2673 in this case the 2 will = Roskill

Thankyou.

Pl see one response in your later post ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Steved" wrote in message
...
Hello from Steved

I would like to please use only the first number as a identifier.

for example 1234, 1543, 1789, 1673 in this case the 1 will = City
for example 2234, 2543, 2789, 2673 in this case the 2 will = Roskill

Thankyou.

Helllo Rowan from Steved

Question please
=IF(LEFT(F6)="1","City",IF(LEFT(F6)="2","Roskill", IF(LEFT(F6)="3","Papakura",IF(LEFT(F6)="4","Wiri", IF(LEFT(F6)="5","Shore",IF(LEFT(F6)="6","Orewa",IF (LEFT(F6)="7","Swanson","")))))))

I would like to add 2 more in ie
IF(LEFT(F6)="8","Panmure",IF(LEFT(F6)="9","Waiheke ",

With your formula you kindly gave me is it possible.

Thankyou.

"Rowan" wrote:

=IF(LEFT(A1)="1","City",IF(LEFT(A1)="2","Roskill", ""))

Regards
Rowan

"Steved" wrote:

Hello from Steved

I would like to please use only the first number as a identifier.

for example 1234, 1543, 1789, 1673 in this case the 1 will = City
for example 2234, 2543, 2789, 2673 in this case the 2 will = Roskill

Thankyou.

Steved,

Posted the response below in your other thread ..
For easier maintenance, and to avoid the IF nesting limits,
you might to want to try the VLOOKUP way ..
---
One way ..

Assuming you have set-up the reference table
in Sheet1, cols A & B, from row1 down as:

1 City
2 Roskill
etc

Then in another sheet, say, Sheet2, if the numbers:
1234, 1543, 1674, 2234, 2543, 2674 etc are in A1 down,
we could put in say, B1:

=IF(A1="","",IF(ISNA(MATCH(LEFT(TRIM(A1))+0,Sheet1 !A:A,0)),"No
match",VLOOKUP(LEFT(TRIM(A1))+0,Sheet1!A:B,2,0)))

and copy B1 down

Col B will return the desired results

Unmatched cases, if any, will return the phrase: No match

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Steved

You can only have 7 nested ifs. To get more your best bet would probably be
to use a Vlookup with a refrence table. Set the table up like this
1 City
2 Roskill
etc

My reference table is in range I1 to J9 so then the vlookup would be
something like:
=VLOOKUP(--LEFT(A1),$I$1:$J$9,2,0)

See http://www.contextures.com/xlFunctions02.html for more details on Vlookups

Regards
Rowan

"Steved" wrote:

Helllo Rowan from Steved

Question please
=IF(LEFT(F6)="1","City",IF(LEFT(F6)="2","Roskill", IF(LEFT(F6)="3","Papakura",IF(LEFT(F6)="4","Wiri", IF(LEFT(F6)="5","Shore",IF(LEFT(F6)="6","Orewa",IF (LEFT(F6)="7","Swanson","")))))))

I would like to add 2 more in ie
IF(LEFT(F6)="8","Panmure",IF(LEFT(F6)="9","Waiheke ",

With your formula you kindly gave me is it possible.

Thankyou.

"Rowan" wrote:

=IF(LEFT(A1)="1","City",IF(LEFT(A1)="2","Roskill", ""))

Regards
Rowan

"Steved" wrote:

Hello from Steved

I would like to please use only the first number as a identifier.

for example 1234, 1543, 1789, 1673 in this case the 1 will = City
for example 2234, 2543, 2789, 2673 in this case the 2 will = Roskill

Thankyou.

Hello Rowan From Steved

Please ignore my ealier post to you as it has been answered
Thanks very much for your time on my issue.

"Rowan" wrote:

=IF(LEFT(A1)="1","City",IF(LEFT(A1)="2","Roskill", ""))

Regards
Rowan

"Steved" wrote:

Hello from Steved

I would like to please use only the first number as a identifier.

for example 1234, 1543, 1789, 1673 in this case the 1 will = City
for example 2234, 2543, 2789, 2673 in this case the 2 will = Roskill

Thankyou.

Hello Max yes I posted it twice by mistake as Microsoft site must have went
off line
hence I thought I had not sent the first one.

Cheers.

"Max" wrote:

Pl see one response in your later post ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Steved" wrote in message
...
Hello from Steved

I would like to please use only the first number as a identifier.

for example 1234, 1543, 1789, 1673 in this case the 1 will = City
for example 2234, 2543, 2789, 2673 in this case the 2 will = Roskill

Thankyou.