Trying to extract text cells at the first instance of four consecutive numbers

You will need to upload an Excel Spreadsheet file with 20-30 rows of de-sensitized data that is representative of the mix you are working with.

And please upload an Excel file and not a screen shot or pic. It saves having to retype data.


If you are not familiar with how to do this:

• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”
• The file name will appear at the bottom of your reply.

Try this in B1 and fill down.

Formula:

Please Login or Register  to view this content.

Then this in C1 and fill down.

Formula:

Please Login or Register  to view this content.

portokie,

Here is what I got with the data supplied using those formulas ... which is what the anticipated output listed above is.

	A	B	C
1	abc def 2016 ghi jkl	abc def	2016 ghi jkl
2	abc def 2015 ghi jkl 3033 mno	abc def	2015 ghi jkl 3033 mno
3	abc def 2014 ghi jkl	abc def	2014 ghi jkl
4	abc def 2012 ghi jkl 2125 mno	abc def	2012 ghi jkl 2125 mno

Would you give us a more comprehensive list of examples?

Try this in B1 instead

Formula:

Please Login or Register  to view this content.

The formula in C1 will remain the same.

Try this...

Data Range

	A	B	C
1
2	abc def 2016 ghi jkl	abc def	2016 ghi jkl
3	abc def 2015 ghi jkl 3033 mno	abc def	2015 ghi jkl 3033 mno
4	abc def 2014 ghi jkl	abc def	2014 ghi jkl
5	abc def 2012 ghi jkl 2125 mno	abc def	2012 ghi jkl 2125 mno
6

This formula entered in B2:

=LEFT(A2,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A2&1234567890))-2)

This formula entered in C2:

=MID(A2,LEN(B2)+2,50)

Select B2:C2 and copy down as needed.

Tony,

It appears OP wants to retain the trailing space in column B.

In that case replace -2 with -1.