hi all

if i have coulm in excel include the followinf dat

334567778
224567890
334567890
123456765
678123678

and i want include new coulm with the following detales

if th first 4 digt is 3345 the cell will be from uae
but if the first 4 digt is 2245 the cell will be from USA
and if first 4 digt is 6781 the cell will from QAT
if the first 4 digt is 6781 the cekk will be from SA



334567778 from uae
224567890 from USA
334567890 from uae
123456765 from SA
678123678 from QAT



how i do that

You can use nested IF if you have only four countries...
Otherwise in another sheet (say Sheet2)
enter the numbers in Col A and corresponding country in Col B
then assuming your numbers are in Col A of Sheet1, enter this in Col B of
Sheet1
=VLOOKUP(LEFT(A1,4),Sheet2!A:B,2,FALSE)
and copy down...

"M.K" wrote:

hi all

if i have coulm in excel include the followinf dat

334567778
224567890
334567890
123456765
678123678

and i want include new coulm with the following detales

if th first 4 digt is 3345 the cell will be from uae
but if the first 4 digt is 2245 the cell will be from USA
and if first 4 digt is 6781 the cell will from QAT
if the first 4 digt is 6781 the cekk will be from SA



334567778 from uae
224567890 from USA
334567890 from uae
123456765 from SA
678123678 from QAT



how i do that

Hi,

Your 3rd and 4th numbers are the same, I'm assuming this is an error:
"if th first 4 digt is 3345 the cell will be from uae
but if the first 4 digt is 2245 the cell will be from USA
and if first 4 digt is 6781 the cell will from QAT
if the first 4 digt is 6781 the cekk will be from SA"

The most flexible way would be to use something like this which could be set
up to handle any number of prefixes:

1. Create a table like this in say G1:H4

3345 UAE
2245 USA
6781 QAT
1234 SA

Then your formula is

=VLOOKUP(--LEFT(A1,4),$G$1:$H$4,2,)

Where the data is in A1:A1000.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"M.K" wrote:

hi all

if i have coulm in excel include the followinf dat

334567778
224567890
334567890
123456765
678123678

and i want include new coulm with the following detales

if th first 4 digt is 3345 the cell will be from uae
but if the first 4 digt is 2245 the cell will be from USA
and if first 4 digt is 6781 the cell will from QAT
if the first 4 digt is 6781 the cekk will be from SA



334567778 from uae
224567890 from USA
334567890 from uae
123456765 from SA
678123678 from QAT



how i do that

=IF(LEFT(A1,4)="3345","uae",IF(LEFT(A1,4)="2245"," usa",IF(LEFT(A1,4)="6781","qat","sa")))


Gord Dibben MS Excel MVP


On Mon, 16 Mar 2009 10:16:10 -0700, M.K
wrote:

hi all

if i have coulm in excel include the followinf dat

334567778
224567890
334567890
123456765
678123678

and i want include new coulm with the following detales

if th first 4 digt is 3345 the cell will be from uae
but if the first 4 digt is 2245 the cell will be from USA
and if first 4 digt is 6781 the cell will from QAT
if the first 4 digt is 6781 the cekk will be from SA



334567778 from uae
224567890 from USA
334567890 from uae
123456765 from SA
678123678 from QAT



how i do that

You could either setup a table (Say in D2:E5) with your correlations
3345 uae
2245 USA
6781 QAT
1234 SA


Then your formula could be:
=VLOOKUP(VALUE(LEFT(A2,4)),$D$2:$E$5,2,FALSE)
and copy down as needed.

The benefit of this setup si that you could easily add additional entries to
your table as needed.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"M.K" wrote:

hi all

if i have coulm in excel include the followinf dat

334567778
224567890
334567890
123456765
678123678

and i want include new coulm with the following detales

if th first 4 digt is 3345 the cell will be from uae
but if the first 4 digt is 2245 the cell will be from USA
and if first 4 digt is 6781 the cell will from QAT
if the first 4 digt is 6781 the cekk will be from SA



334567778 from uae
224567890 from USA
334567890 from uae
123456765 from SA
678123678 from QAT



how i do that