Excel to get certain text from data

[mark888]

Hello All,

Sorry to bother you guys. Kinda need some help on a formula.
I have an attached file where I have loads of data in column J. I need it to only locate a number starting with C00* and display it in column L. I tried using array formula and Find formula but it still does not get me the value I need.

I have put the Example output in column L as how I need it. Is this possible to be done?
If there is multiple C00* then it should also display that and if there is none found then show as "Not Found".

Hope someone is able to help. :-)

Kind Regards,
Mark.

[xlnitwit]

Hi,

Is VBA allowed for the solution? It will make returning multiple matches much simpler I believe. Also, are all the codes the same length as they appear to be in your example data?

[mark888]

Hi,

Is VBA allowed for the solution? It will make returning multiple matches much simpler I believe. Also, are all the codes the same length as they appear to be in your example data?

Hi xlnitwit,

Would be great if there is such a macro which can do this. Would make my life easier but I'm not sure how to do so. :-(

Kind Regards,
Mark.

[xlnitwit]

It looks like the same code can appear several times in one cell? In such a case, do you want the function to return it multiple times or should it only return a list of unique values?

This code will return all matches regardless but would need amending to return unique values only:

Please Login or Register  to view this content.

You need to add the code to a normal module and then call it from a cell:
=MatchNumbers(J2)
for instance.

[mark888]

It looks like the same code can appear several times in one cell? In such a case, do you want the function to return it multiple times or should it only return a list of unique values?

Hi xlnitwit,

Yes it should only capture the unique values in each cell. :-)

Kind Regards,
Mark.

[xlnitwit]

All right then - here is the revised function code:

Please Login or Register  to view this content.

[xlnitwit]

By the way, you didn't answer my earlier question but if all the codes are the same length, you could amend the pattern in the function code to use:

Please Login or Register  to view this content.

[mark888]

All right then - here is the revised function code:

Please Login or Register  to view this content.

Hi xlnitwit,

Tried that and it returns a failed value #NAME?. Npt sure if im doing this correctly?

Kind Regards,
Mark.

[mark888]

Hi xlnitwit,

Tried that and it returns a failed value #NAME?. Npt sure if im doing this correctly?

Kind Regards,
Mark.

Hi xlnitwit,

Yes I tried it again. I made a mistake. It works perfectly as a function. Many thanks sir for your help. :-)

Kind Regards,
MArk.

[xlnitwit]

You are most welcome.