Please help to search a custom format
in [General]
|
From: pol on 27 Apr 2010 01:15 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
From: Jacob Skaria on 27 Apr 2010 01:21 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
From: pol on 27 Apr 2010 04:38 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
From: Jacob Skaria on 27 Apr 2010 05:08 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
From: pol on 27 Apr 2010 05:36
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 |