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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Office Productivity

From novice to tech pro — start learning today.