Extracting Piece Of Text Having Specific Pattern From A Larger Text String

[nomanfiroz]

0 is added just to ensure that there are no errors. Everything else can be found out by evaluating the formula. Post back if that's still not clear.

Hi,
I liked your thread but I have a different query.

I have the following data in excel:

Column A Column B
Safe and sound A0001199 Safe
London Cars S0100999 LC
Wembley Motors Q0001988 We
And so on…

In column B, I need to extract A0001199 (in cell B1), S0100999 (in cell B2) and Q0001988 (in cell B3).

Please could you help, how I can extract those data from column A to column B?

Thank you in advance
Firoz

[Excel Fox]

hi nomanfiroz, please start your own thread with specific and meaningful thread titles, unless it is exactly what you are looking for (in which case the solution would have already been provided), and do not hijack another thread. If the context of the thread is in line with your query, you can post a link of that thread in your new thread. I have moved your post to a new thread this time.

coming to your query, can you confirm that the text that you want to extract follows a specific pattern, ie, one characters followed by 7 numbers, or in pattern languange, ?#######

[Excel Fox]

Assuming my assumption above is right, here's a classic lazy one. You could find many better variants around if you searched well enough though.

=MID(SUBSTITUTE(TRIM(A1)," ","|",LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))-1),FIND("|",SUBSTITUTE(TRIM(A1)," ","|",LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))-1))+1,8)

If I were to add another assumption that the first numeric character in the entire text string will be the one immediately after the first character of the text patter you want to extract, then this will also work

=MID(A1,SUMPRODUCT(MIN(IFERROR(FIND(ROW(OFFSET($A$ 1,,,10,))-1,A1),FALSE)))-1,8)

[nomanfiroz]

Dear Administrator,

Thank you very much for your reply.

When I enter the formula [=MID(A1,SUMPRODUCT(MIN(IFERROR(FIND(ROW(OFFSET($A$ 1,,,10,))-1,A1),FALSE)))-1,8) ], sometimes it picks the data and sometime it does not. For example:

Column A Column B Column C
Narratio Acc no Comment

BARFRD H LTD Z0001670 Z0001670 Picked
SJ&EM GRIGG S J GRIGG E3004951 3004951 Not picked
ETW GAR T/AS SW E3000055 FP 27/08/13 1039 RP4679963388259900 E3000055 3000055 Not picked
RAMORA LIMITED E0011232 FP 24/08/13 0505 53050526501454000N E0011232 E0011232 Picked
SUITOR MOTORS SUITORS N0001147 N0001147 Picked
MOTORSPORTL N4007070 4007070 Not picked
SAGEPARTS GSE LTD B230813 30813 Not picked

Often any account number beginning with E does not pick. I am sorry I do not understand or know how to fix it. Please could you help. Your help and support will be highly appreciated.

Many thanks in advane and kind regards,
Firoz

[snb]

Based on your examples:

=LEFT(MID(A1,FIND("0",A1)-1,100),FIND(" ",MID(A1,FIND("0",A1)-1,100)&" "))

[nomanfiroz]

Dear SNB

Sorry, it gave me the exact same result as follows:

I have data in column A, result in column B and comment (Optional) in column C

BARFRD H LTD Z0001670 Result Z0001670 Comment Picked
SJ&EM GRIGG S J GRIGG E3004951 Result 3004951 Comment Not picked
ETW GAR T/AS SW E3000055 FP 27/08/13 1039 RP4679963388259900 E3000055 Result 3000055 Comment Not picked
RAMORA LIMITED E0011232 FP 24/08/13 0505 53050526501454000N E0011232 Result E0011232 Comment Picked
SUITOR MOTORS SUITORS N0001147 Result N0001147 Comment Picked
MOTORSPORTL N4007070 Result 4007070 Comment Not picked
SAGEPARTS GSE LTD B230813 Result 30813 Comment Not picked

Sometime it did pick, sometime it did not. We need that 8 digits account number (Like as Z0001670) to compare two sets of data. Please could you help?

Many thanks in advance
Firoz

[Admin]

Give this a try.

=MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456 789"))-1,8)

[nomanfiroz]

It worked absolutely fine.
Thank you so much.

I have saved 24 hours working time today.

Thank you very much and kind regards,
Firoz