Need formula to separate data from column and send to another

The following line (below) appears in an Excel spreadsheet.  I need to copy the codes (A code and L code) from this column and place them into separate columns within the same sheet.  I am exporting from a billing program and then need the codes in their own columns in order to import into another program.

"A106 Communicate (with client) L330 Depositions"


Example:

"A106"...in one column and "L330" in another column within my sheet.  Can leave the original column that contains text and codes in the sheet.  Is there a formula that would do this for me.
CynSzczAsked:
Who is Participating?
 
barry houdiniCommented:
You can try this formula to extract the "A" code, assuming it's always followed by 3 digits, and where your data is in B2 (change as required)

="A"&TEXT(MATCH(2^15,INDEX(SEARCH("A"&TEXT(ROW(INDIRECT("1:1000"))-1,"000"),B2),0))-1,"000")

and this for "L"

="L"&TEXT(MATCH(2^15,INDEX(SEARCH("L"&TEXT(ROW(INDIRECT("1:1000"))-1,"000"),B2),0))-1,"000")

If those don't work please give some representative examples of your data - will the codes always have 3 digits, is there any consistency about where they appear in the text?

regards, barry
0
 
CynSzczAuthor Commented:
Thanks Barry, I am going to try this.  I will let you know.  Yes, the column with the codes will always be the same.
0
 
CynSzczAuthor Commented:
Barry!!! It works.  One thing though.  The sheet may have two or two hundred rows so the "B2" reference will not produce results for the entire sheet.  How do I solve that?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.