How to extract a set of 6 numbers from a set of 7 to 15 numbers.

[john84811]

Earlier I had alpha numeric data. I looked up a solution on your website and deleted all the text from the alpha-numbers (http://www.mrexcel.com/forum/showthread.php?t=545660) (Thanks to respondents, i learnt something new.)

Now this is where i am. I have the below mentioned data with me.


6600042
25600032
83483600042
149600017
17632600092
4981184600078

The data that i want to extract from this range has the following pattern.
The set of numbers i am looking for from this string all have 600 as the first 3 numbers. I need to return the 600 value and the next 3 numbers next to it.

So my output from the above data would then be

600042
600032
600042
600017
600092
600078

Thanks and your response would be much appreciated.

 

[NBVC]

Are they all at they all of that pattern where the number is the last 6 digits...

e.g. =RIGHT(A1,6)+0

if not, then:

=MID(A1,FIND("600",A1),6)+0

 

[SQUIDD]

this works for me data in a1

=MID(A1,FIND("600",A1&"600")*1,20)

dave

 

[sbsjr]

If the first value 6600042 is in cell A1, try this:

=MID(TEXT(a1,"####"),FIND(600,TEXT(a1,"####")),6)

Hope that helps.

 

[T. Valko]

Earlier I had alpha numeric data. I looked up a solution on your website and deleted all the text from the alpha-numbers (http://www.mrexcel.com/forum/showthread.php?t=545660) (Thanks to respondents, i learnt something new.)

Now this is where i am. I have the below mentioned data with me.


6600042
25600032
83483600042
149600017
17632600092
4981184600078

The data that i want to extract from this range has the following pattern.
The set of numbers i am looking for from this string all have 600 as the first 3 numbers. I need to return the 600 value and the next 3 numbers next to it.

So my output from the above data would then be

600042
600032
600042
600017
600092
600078

Thanks and your response would be much appreciated.

Maybe this...

Book1
	A	B
2	6600042	600042
3	25600032	600032
4	83483600042	600042
5	149600017	600017
6	17632600092	600092
7	4981184600078	600078
Sheet1

This formula entered in B2 and copied down:

=--MID(A2,FIND(600,A2),6)

 

[john84811]

Maybe this...

Book1
*	A	B
2	6600042	600042
3	25600032	600032
4	83483600042	600042
5	149600017	600017
6	17632600092	600092
7	4981184600078	600078
Sheet1

This formula entered in B2 and copied down:

=--MID(A2,FIND(600,A2),6)

Thank you. That worked.

 

[T. Valko]

Thank you. That worked.

You're welcome. Thanks for the feedback!