Function to extract code from text cells with no discernible patterns

[mmallari]

Hi Everyone,

I would really appreciate your help on this, I am not very advance in Excel, but if there is anyway the Unit codes can be extracted from these set of cells instead of having to manually do it to around 3000 random entries it would really be a life saver.

This work relates to units taught in UEE32211 E103A, E105A, J102A & J108A

Upon successful completion of this subject, students should: be able to discuss theoretical, regulatory, and practical factors that influence training within the Australian vocational education and training sector; be able to discuss the vocational training system in the context of the training development cycle; and be able to design, conduct and present the findings of a small basic research project focusing on one aspect of the training cycle that addresses an issue of importance in your area of professional practice.

This relates directly to my online delivery of units from TAE40116.

This relates to all our TAS and the Units that we deliver

UEENEEE104ASolve problems in d.c. circuits UEENEEG101ASolve problems in electromagnetic circuits and associated devices UEENEEG102ASolve problems in LV a.c. circuits UEENEEF102AInstall and maintain cabling for multiple access to telecommunication services

UEENEEF102AInstall and maintain cabling for multiple access to telecommunication services

RIIWHS205E What do u mean by this can you be specific to what would be examples of suporting docs

I am aiming for the result to look like either of these:

UEE32211	UEE32211
TAE40116	TAE40116
UEENEEE104A	UEENEEE104A UEENEEG101A UEENEEG102A UEENEEF102A
UEENEEG101A	UEENEEF102A
UEENEEG102A	RIIWHS205E
UEENEEF102A
UEENEEF102A
RIIWHS205E

Hope I am making sense and any help would be appreciated, even a straight up answer "No it can't be done" atleast I know I am not dreaming

Cheers!

 

[Rick Rothstein]

Do your codes always start with 3 upper case letters?
If so, will the codes be the only text that ever starts with 3 upper case letters?

 

[mmallari]

Do your codes always start with 3 upper case letters?
If so, will the codes be the only text that ever starts with 3 upper case letters?

Hi Rick,

No, unfortunately the codes are formatted differently too.

They are however, the only combination of uppercase and numeric values in the cell, the rest are actual words. These are the codes that I hope to extract from the sample list above.

UEE32211

TAE40116

UEENEEE104A UEENEEG101A UEENEEG102A UEENEEF102A

UEENEEF102A

RIIWHS205E

 

[Rick Rothstein]

Hi Rick,

They are however, the only combination of uppercase and numeric values in the cell, the rest are actual words.

Your first example cell text was this...

This work relates to units taught in UEE32211
E103A, E105A, J102A & J108A

The E103A, E105A, etc. are not words and are composed of upper case letters and digits only, but they apparently not codes because you did not list them in the output you showed us. That is why I asked you about the leading 3 upper case letters... to be able to distinguish codes from those particular non-codes. Are you now saying that was a bad example and that we will not see text like E103A in your cells?

 

[Peter_SSs]

Further to Rick's comments ..

They are however, the only combination of uppercase and numeric values in the cell,

UEENEEE104ASolve problems in d.c. circuits

UEENEEE104AS is a combination of upper case and numeric from the above sample, yet you only want UEENEEE104A extracted.
Since Excel does not recognise "words" like a human does, how should the decision be made as to where to end the code?

What we are trying to do is to get a strict set of 'rules' in words as to what should be extracted and then we can see if that can be accomplished with Excel.