Find Partial text in an array and extract charaters to right

Hi,

Since i am newbie, please help on below query

I have "& Name: 24825" in a cell

I want to search "& Name: " in an array from colmn A to G and once found i want to extract 5 characters after that

Thanks,
Jenita

perhaps:
=MID(CONCATENATE(A1,B1,C1,D1,E1,F1,G1),SEARCH("& Name: ",CONCATENATE(A1,B1,C1,D1,E1,F1,G1),1)+8,5)

Hey Yudlugar,
This is not Working. Could you please help me out with Another Alternative

"& Name: 24825" This Could be in any cell from Column A to F

I want to search partial text "& Name: " this will be constant and then my final answer would be 24825

Hi:
in A1, B1, D1, E1 and F1 enter "Random Text"
in C1 enter "& Name: 24825"
in G1 enter =MID(CONCATENATE(A1,B1,C1,D1,E1,F1),SEARCH("& Name: ",CONCATENATE(A1,B1,C1,D1,E1,F1),1)+8,5)

The result in G1 is 24825.

If it is still not working can you please upload an example workbook with the data for which it does not work (click go advanced and then attachments).

Hey Yudlugar,
This works now, My only concern is this text could be in Any cell from Column A to F.
Hence Instead of Concatenate , is it possible to define and array to search

how about:
=SUM(IF(ISERROR((FIND("& Name: ",A2:F25))),0,1*RIGHT(A2:F25,5)))
as an array formula (confirm with ctrl+shift+enter).

It relies on a couple of assumptions about your data:
the 5 digits that you want are a number and are always at the end of the text in the cell. It works on the example you provided.

Hey Yudlugar,
I tried pasting the formula, I can retrive it as "0"
May i please request you to attach the exel Sheet. Sorry for the inconvinience

See cell A29 in attachment.

I think maybe you did not confirm as an array formula (ctrl+shift+enter)? You should see {} brackets around the formula if it is entered correctly.

Hey Yudlugar,

This is just Perfect , I was trying to paste in a cell inj the Array Specific. Hence It was retriving "0" and giving me a circular reference warning.

Thank you so much for your help. This now works just perfect

No problem, sorry it took a few goes to understand what you meant!

No Worries.
All is well, That ends well. Have a nice day ahead