combining text of multiple cells in one automatically

I am trying to look for a easy way to combine field together. Added 2 files one is how it is imported (just a small portion) and one is how I want it to be. Any idea on how I can get this done automated? Because I later on need to be able to search on the info.

hscodeneedstobe.xlsx
hscodeold.xlsx

Can't see how you can do it with a worksheet function, but I am often amazed at the ingenuity of the guys here.

You could do it with a User Define Function (UDF). For example, if you copy the following code to a standard VBA module, you can put, in cell E1,

Formula:

Please Login or Register  to view this content.

and copy it down to the end of the data. You can then select all the data in column E, copy it, and Paste Special | Values over the original data in column B.

Please Login or Register  to view this content.

Please note that this UDF will NOT reflect any changes made after the function has been evaluated. You would need to re-enter the formula and copy it down. I suspect that you won't need to do that though.



Regards, TMS

Or you may try this....

Please Login or Register  to view this content.

Click the button on Test sheet to get the desired output.

Both demo sheet solutions worked like a charm, thank you both.

You're welcome. Thanks for the rep.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.