Re: Extract numbers after specific text in a text string
Istvan Hirsch, Thanks and appreciate it....
Re: Extract numbers after specific text in a text string
Istvan Hirsch, Thanks and appreciate it....
Re: Extract numbers after specific text in a text string
Hi Istvan,
Can u please explain how the below formula works, i am bit new to such big formulas... thx...
Quote from samshan143;705846Dear Istvan Hirsch,
your formula works perfectly thanks.. However, i suppose instead of FIND(" SN ",....) i can use SEARCH (" sn ",...) since at times SN could be small letters as well ? btw is spaces required in " SN " ?
Re: Extract numbers after specific text in a text string
Quote from István Hirsch;705823The formula above collects all the numbers in the string, for example, for "abc 23 SN 12345 xyz" provides "2312345". Moreover, if "SN" occurs in words it should be ignored.
Try this formula, if strings are in col A, put into B1:
=LOOKUP(10^6,1*MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789",FIND(" SN "," "&A1&" "))),{2,3,4,5,6}))
Dear Istvan Hirsch,
your formula works perfectly thanks.. However, i suppose instead of FIND(" SN ",....) i can use SEARCH (" sn ",...) since at times SN could be small letters as well ? btw is spaces required in " SN " ?
Hi,
I am trying to extract numbers after a specific text in a text, for eg :
abc SN 12345 xyz
edf SN No. 456 mno
As per above, i want to extract any numbers afters "SN". the numbers can be vary in digits i.e. it can be 3 digit numbers or 4 or 6. Also, at times there is some other text in between (like SN No.) numbers and search word (i.e. SN)
Any formula to get result as "12345" and "456".
please prefer formula instead of VBA, since i am not sure how to use VBA.
thanks
Re: filter specific number is text string
Quote from pike;702969You can use wild cards in the contains filter
* 403 *
hi,
i tried that (* 403*), however, in that case it is returning with zero results (i.e. no 403 in any columns) pls help.
Re: filter specific number is text string
Quote from apo;702965
sorry but i dont know to use VBA and prefer to use any fitler with desired results. thnx
Re: filter specific number is text string
yes i tried that too, but its not working, any other ways pls ?
Hi,
I want to filter a specific number in a series of column, presently I tried to filter using CONTAINS but it also shows result numbers prefixed or suffixed alongwith the filtered numbers. Let me explain with an example.
suppose i want to filter 403 in following column
[TABLE="width: 500"]
abc 403 xyz
[/td][/tr][tr][td]def 60403 hij
[/td][/tr][tr][td]ccc 12345 ffg
[/td][/tr]
[/TABLE]
in above if i want to filter only 403 and if i use filter using CONTAINS 403, then the result will show both the columns i.e. one with 403 and also 60403.
is there any way where if i use contain filter for 403 it should show filtered result of 403 only and NOT 60403 or like 4030 etc.
please assist.
regds
Re: Different Date Format
may be wat u said is right, so hw do i chnge into one single format ?
Hi,
I have a worksheet where in a column, dates are stored in various date format i.e. it may be DMY or MDY or YMD.
I have tried using changing date thru format cell or text to column function, but i get following result. As such the data is only for the month of AUG but after conversion it shows Jan, Feb, Mar etc.
[TABLE="width: 155"]
[tr][td]Before
[/td][td]After
[/td][/tr][tr][td]31/07/2012
[/td][td]31/07/2012
[/td][/tr][tr][td]31/07/2012
[/td][td]31/07/2012
[/td][/tr][tr][td]31/07/2012
[/td][td]31/07/2012
[/td][/tr][tr][td]31/07/2012
[/td][td]31/07/2012
[/td][/tr][tr][td]08-01-12
[/td][td]8-Jan-12
[/td][/tr][tr][td]08-01-12
[/td][td]8-Jan-12
[/td][/tr][tr][td]08-02-12
[/td][td]8-Feb-12
[/td][/tr][tr][td]08-03-12
[/td][td]8-Mar-12
[/td][/tr][tr][td]08-03-12
[/td][td]8-Mar-12
[/td][/tr][tr][td]08-06-12
[/td][td]8-Jun-12
[/td][/tr][tr][td]08-06-12
[/td][td]8-Jun-12
[/td][/tr][tr][td]08-06-12
[/td][td]8-Jun-12
[/td][/tr][tr][td]08-06-12
[/td][td]8-Jun-12
[/td][/tr][tr][td]08-06-12
[/td][td]8-Jun-12
[/td][/tr][tr][td]08-06-12
[/td][td]8-Jun-12
[/td][/tr][tr][td]08-07-12
[/td][td]8-Jul-12
[/td][/tr][tr][td]08-07-12
[/td][td]8-Jul-12
[/td][/tr]
[/TABLE]
I have attached the excel sheet i'm working on. Is there any formula whr it can identify date format and convert into single date format ?
Hi,
I have a data with numbers, which ideally should in series of 50s (i.e. 250-300 or 35000-35050, etc etc). I have attached the sample sheet of numbers,
what result i expect is that,
1) which numbers are missing
2) what is the range of this missing numbers in the series of 50s (i.e. suppose missing number is 65 then range shuld be 50-100)
pls guide.
Sam
Re: find numbers from various rows/ columns
Dear AAE,Can you pls look into above and advise ?Thx
Re: find numbers from various rows/ columns
I want a lookup formula so that i can get this numbers (the one in the first column). It is also ok for me, if formula can return TRUE or FALSE (i.e. if number is there in other columns it returns TRUE or else FALSE)
The eg i gave is exact dummy data i have with me.
Thx
Hi,
I am trying to find some number which i have from a list of numbers available in various rows, for eg
[TABLE="width: 384"]
[tr]
[TD="width: 64, align: right"][TABLE="width: 384"]
[TD="width: 64"] [/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]38[/TD]
[/TABLE]
[/TD]
[TD="width: 64"][/TD]
[TD="width: 64, align: right"][/TD]
[TD="width: 64, align: right"][/TD]
[TD="width: 64, align: right"][/TD]
[TD="width: 64, align: right"][/TD]
[TD="align: right"][/TD]
[/TABLE]
so as per above, i am tryng to find 11,13,14,15,16.... 38 from columns given i.e. 7,11,13,17....
pls assist
rdgs/ Sam Shan
Re: match a cell value from other sheet in a text string
Dear NBVC,
As per above formula, though it works well in most cases, but still there are 2 problems as follows:
1) it is not giving result with text strings mentioning only DN (i.e. suppose if cell has text with DN 12345, then it results #value).
2) In some cases, it returned result with commas, semicolon (of course, this were there in text strings as DN :12345,).
Anything we can do to filter them out?
Thx..
Re: match a cell value from other sheet in a text string
Dear NBVC,
Sorry, i made an mistake while using that formula in another sheet... Thx...
But, i have new prblom if u can help...
I am trying to extract some numbers after specific word in a cell using SEARCH function, but the problem is, it extracts other characters/ text as well. here's an example...
1st cell value: delivered 30 pcs as per DN 1234 dtd: 12/12/12
2nd cell value: delivered vide DN No. 123456 30 pcs
Here, I want to extract DN no. in other column, using SEARCH function, but it extracts additional characters/ numbers after space or incomplete numbers, if there is some more text characters after search word DN (like in case of 2nd cell value, it will extract only 123 instead of 123456).. basically, i want to extract only numbers.
Is there any way i can get this result ??
thx in advance....
Re: match a cell value from other sheet in a text string
Dear NBVC,
In same above case, what if i need to get FIRST MATCH and return the corresponding item ??
Pls assist..
Thx
Re: finding range of a given series
Mr Stephen,
Yes, you are right, but that is the prblm...
Re: finding range of a given series
Mr StephenR....
Thnks bt can u pls elaborate, i am not quite getting it..
Re: finding range of a given series
thnks cytop, but the result i expect is 1 to 10 and then 120 to 150, whereas in below formula i get only one result i.e. 1 to 150.
pls let me knw if there is any other way to get this results....
thx & rgds...
Quote from cytop;639335Unless you mean you simply want the Min and Max values in the range...
[bf]
=MIN(A12:A20) &" to " & MAX(A1:A20)
[/bf]