How to Connect a Word from a Phrase

I have been doing this task for about three (3) months. And I really hate doing this as it really eats a lot of time. I have mentioned once that I am already an advanced MS Excel user but I had difficulty in this one.

I am retyping one by one, in a new column each vendor’s name I will find from the description of a transaction. Here are the examples of the transactions:

0.02 Import TYCO ELECTRONICS 8808246521 AIR 714-1050-Z 61245

CV#40727-EMKA BESCHLAGTEILE AG5141673 /

0.02-Import,ELCOMPONICS,7AFV047,AIR,0W2670A,APV60709

0.02,Import,ELCOMPONICS,7AFV047,AIR,0W2670A,APV60709

2 10,Import,FCI OEN,7AFY554,AIR,730-0204-Z,APV61273

10% SUBCON COST ALLOCATION (NTBR), FEBRUARY 2010

0.02,Import,MOLEX,4AO4768,AIR,113705901,APV62212

0.02 Import JTK 7AGP385 AIR 411-0011A 61259

From there I have to come up reclassifying each line into AIR-TYCO; AIR-EMKA etc. Basically I have to capture the vendors name and have it added to either Air- or Sea-.

Just imagine at least three thousand transactions each month and see how the format differs from one another. It is really time consuming to type it all. I have already tried a lot of data sorting, copy and paste functions but this one is the most helpful for me. Thanks to Usyo for teaching me this technique.

Today I have learned about the MID function and this is what my office mate has taught me to do.

In a single cell, type the prefix to be used. In this case it should be AIR-. Assuming you put it in cell d1.

To the right column next to the description, type the following =d1&MID(cell of the transaction(e.g.c3),number of characters you would want to cut (e.g.12),number of characters you want to be added(e.g.10). Make sure to make d1 constant. Press F4 in between characters to make it d$1$.

With this, assuming we work on the first transaction the result would be AIR-TYCO ELECT. I just have to copy the formula until the end match of the transactions and my work is almost done in just a matter of seconds.

However, it did not work perfectly matching with the format I wanted but at least I need not type the words one by one anymore. Sorting, Copy and Paste functions will further take me to the uniform format to finalize my report.

Actually the most important part here is the fact that I was able to know the use of MID function. I didn’t even know that it is in fact existing from Excel features.

If you have better recommendations, please let me know. Thank you.