the column A1 has custom format 000-000 having the data like this
123-456
001-789
313-245
456-779
084-321

The column B1 has the following data
ABC123456
DSD001789
HGH084321
YYY343434

like this 7500 record exist. I want to find column B1 value (numeric ) in
Column A1. So ABC123456,DSD001789, HGH084321 exist in A1 . How I can search
that numeric part in column A1. Pls help . Is there any function to give in
formula bar.
or Is it possible to write CTR+F, CTRL V to find automatcially in ceach
cell. Please advice

With thanks
Pol

In cell C1 apply the below formula and copy down as required.

=IF(ISNA(MATCH(TEXT(MID(B1,4,10),"000-000"),A:A,0)),"","Exist in Column A")


--
Jacob (MVP - Excel)


"pol" wrote:

the column A1 has custom format 000-000 having the data like this
123-456
001-789
313-245
456-779
084-321

The column B1 has the following data
ABC123456
DSD001789
HGH084321
YYY343434

like this 7500 record exist. I want to find column B1 value (numeric ) in
Column A1. So ABC123456,DSD001789, HGH084321 exist in A1 . How I can search
that numeric part in column A1. Pls help . Is there any function to give in
formula bar.
or Is it possible to write CTR+F, CTRL V to find automatcially in ceach
cell. Please advice

With thanks
Pol

Thanks for the help . It is working but still have small problem. I applied
you formula as follows

=IF(ISNA(MATCH(D5,Sheet1!C:C,0)),"Not",Sheet1!A6)

If is found I want to write the corresponding data on the next column. Here
if the data is found in sheet1!A4 column , the output data will write from
A6. I want to write the data of the next cell of the same row

please advice



"Jacob Skaria" wrote:

In cell C1 apply the below formula and copy down as required.

=IF(ISNA(MATCH(TEXT(MID(B1,4,10),"000-000"),A:A,0)),"","Exist in Column A")


--
Jacob (MVP - Excel)


"pol" wrote:

the column A1 has custom format 000-000 having the data like this
123-456
001-789
313-245
456-779
084-321

The column B1 has the following data
ABC123456
DSD001789
HGH084321
YYY343434

like this 7500 record exist. I want to find column B1 value (numeric ) in
Column A1. So ABC123456,DSD001789, HGH084321 exist in A1 . How I can search
that numeric part in column A1. Pls help . Is there any function to give in
formula bar.
or Is it possible to write CTR+F, CTRL V to find automatcially in ceach
cell. Please advice

With thanks
Pol

A bit confused with the formula you have posted...Need more info..

--
Jacob (MVP - Excel)


"pol" wrote:

Thanks for the help . It is working but still have small problem. I applied
you formula as follows

=IF(ISNA(MATCH(D5,Sheet1!C:C,0)),"Not",Sheet1!A6)

If is found I want to write the corresponding data on the next column. Here
if the data is found in sheet1!A4 column , the output data will write from
A6. I want to write the data of the next cell of the same row

please advice



"Jacob Skaria" wrote:

In cell C1 apply the below formula and copy down as required.

=IF(ISNA(MATCH(TEXT(MID(B1,4,10),"000-000"),A:A,0)),"","Exist in Column A")


--
Jacob (MVP - Excel)


"pol" wrote:

the column A1 has custom format 000-000 having the data like this
123-456
001-789
313-245
456-779
084-321

The column B1 has the following data
ABC123456
DSD001789
HGH084321
YYY343434

like this 7500 record exist. I want to find column B1 value (numeric ) in
Column A1. So ABC123456,DSD001789, HGH084321 exist in A1 . How I can search
that numeric part in column A1. Pls help . Is there any function to give in
formula bar.
or Is it possible to write CTR+F, CTRL V to find automatcially in ceach
cell. Please advice

With thanks
Pol

Ok.

I have two sheet.

Sheet1
Code Mapp
C D
301003 301-006
301005 739-245
301006 753-229
301007 739-247
301008 739-249
301009 802-435
301010 736-111

Sheet2
column D
301-006
000-050
000-051
000-052
753-229
000-056
000-060
000-061
000-070
000-072
000-075

Output
Sheet2
column D

301-006 301003
000-050 Not Exist
000-051 Not Exist
000-052 Not Exist
753-229 301006
000-056
000-060
000-061
000-070
000-072
000-075

Please advice me to use the function
IF(ISNA(MATCH(D2,Sheet1!D:D,0)),"Not",Sheet1!C2)

"pol" wrote:

the column A1 has custom format 000-000 having the data like this
123-456
001-789
313-245
456-779
084-321

The column B1 has the following data
ABC123456
DSD001789
HGH084321
YYY343434

like this 7500 record exist. I want to find column B1 value (numeric ) in
Column A1. So ABC123456,DSD001789, HGH084321 exist in A1 . How I can search
that numeric part in column A1. Pls help . Is there any function to give in
formula bar.
or Is it possible to write CTR+F, CTRL V to find automatcially in ceach
cell. Please advice

With thanks
Pol

Apply the below formula in Sheet2 cell E1

=IF(ISNA(MATCH(D1,Sheet1!D:D,0)),"No",
INDEX(Sheet1!C:C,MATCH(D1,Sheet1!D:D,0)))

--
Jacob (MVP - Excel)


"pol" wrote:

Ok.

I have two sheet.

Sheet1
Code Mapp
C D
301003 301-006
301005 739-245
301006 753-229
301007 739-247
301008 739-249
301009 802-435
301010 736-111

Sheet2
column D
301-006
000-050
000-051
000-052
753-229
000-056
000-060
000-061
000-070
000-072
000-075

Output
Sheet2
column D

301-006 301003
000-050 Not Exist
000-051 Not Exist
000-052 Not Exist
753-229 301006
000-056
000-060
000-061
000-070
000-072
000-075

Please advice me to use the function
IF(ISNA(MATCH(D2,Sheet1!D:D,0)),"Not",Sheet1!C2)

"pol" wrote:

the column A1 has custom format 000-000 having the data like this
123-456
001-789
313-245
456-779
084-321

The column B1 has the following data
ABC123456
DSD001789
HGH084321
YYY343434

like this 7500 record exist. I want to find column B1 value (numeric ) in
Column A1. So ABC123456,DSD001789, HGH084321 exist in A1 . How I can search
that numeric part in column A1. Pls help . Is there any function to give in
formula bar.
or Is it possible to write CTR+F, CTRL V to find automatcially in ceach
cell. Please advice

With thanks
Pol