Posts by samshan143

samshan143 · Mar 14th 2014

Re: Extract numbers after specific text in a text string

Istvan Hirsch, Thanks and appreciate it....

samshan143 · Mar 13th 2014

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;705846

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 " ?

samshan143 · Mar 13th 2014

Re: Extract numbers after specific text in a text string

Quote from István Hirsch;705823

The 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 " ?

samshan143 · Mar 12th 2014

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

samshan143 · Feb 19th 2014

Re: filter specific number is text string

Quote from pike;702969

You 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.

samshan143 · Feb 19th 2014

Re: filter specific number is text string

Quote from apo;702965
If you data is in column A (with a header in Row 1)..
Code
Private Sub CommandButton1_Click()
    With Range("A1").CurrentRegion
        .AutoFilter 1, "* 403 *"
    End With
End Sub

sorry but i dont know to use VBA and prefer to use any fitler with desired results. thnx

samshan143 · Feb 19th 2014

Re: filter specific number is text string

yes i tried that too, but its not working, any other ways pls ?

samshan143 · Feb 17th 2014

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"]

[tr]

[td]

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

samshan143 · Apr 16th 2013

Re: Different Date Format

may be wat u said is right, so hw do i chnge into one single format ?

samshan143 · Apr 15th 2013

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 ?

samshan143 · Mar 15th 2013

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

samshan143 · Mar 6th 2013

Re: find numbers from various rows/ columns

Dear AAE,Can you pls look into above and advise ?Thx

samshan143 · Mar 3rd 2013

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

samshan143 · Mar 1st 2013

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"]

[tr]

[TD="width: 64"] [/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]

[/tr]

[tr]

[TD="align: right"]11[/TD]

[td][/td]

[TD="align: right"]7[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]17[/TD]

[/tr]

[tr]

[TD="align: right"]13[/TD]

[td][/td]

[TD="align: right"]6[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]38[/TD]

[/tr]

[tr]

[TD="align: right"]14[/TD]

[td][/td]

[TD="align: right"]3[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]26[/TD]

[/tr]

[tr]

[TD="align: right"]15[/TD]

[td][/td]

[TD="align: right"]4[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]28[/TD]

[/tr]

[tr]

[TD="align: right"]16[/TD]

[td][/td]

[TD="align: right"]5[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]29[/TD]

[/tr]

[tr]

[TD="align: right"]22[/TD]

[td][/td]

[TD="align: right"]2[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]35[/TD]

[/tr]

[tr]

[TD="align: right"]21[/TD]

[td][/td]

[TD="align: right"]1[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]23[/TD]

[/tr]

[tr]

[TD="align: right"]35[/TD]

[td][/td]

[/tr]

[tr]

[TD="align: right"]38[/TD]

[td][/td]

[/tr]

[/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]

[/tr]

[tr]

[TD="align: right"][/TD]

[td][/td]

[/tr]

[/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

samshan143 · Jan 3rd 2013

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..

samshan143 · Jan 3rd 2013

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....

samshan143 · Jan 3rd 2013

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

samshan143 · Dec 6th 2012

Re: finding range of a given series

Mr Stephen,

Yes, you are right, but that is the prblm...

samshan143 · Dec 6th 2012

Re: finding range of a given series

Mr StephenR....

Thnks bt can u pls elaborate, i am not quite getting it..

samshan143 · Dec 6th 2012

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;639335

Unless you mean you simply want the Min and Max values in the range...
[bf]
=MIN(A12:A20) &" to " & MAX(A1:A20)
[/bf]