Learning Microsoft Excel

[lobogotti]

Concatenate.

In this case junk had a Column A with a certain number sequence that he wanted to use.

Example......

GTH-12-001
GTH-12-002
GTH-12-003
GTH-12-004
GTH-12-005
GTH-12-006

Basically he was wanting to make this go on for hundreds maybe thousands of lines.

Instead of typing these in, like 98% of people will do, you can use a Concatenate feature.

I would start of with a blank Excel Sheet.

In column B, simply type GTH-

In column C, type 12-

In column D, type 001......(important to note, if this is not formated right it will say 1 and both preceding zeros will be lost. You may need to type ' in front if your are not properly formatted to hold the zero's).

Underneath the 001, in the next cell a quick formula such as =D1+1, and then a copy down to each following cell will give you this .........

001
002
003
004
005

so on and so forth.

By this time you can copy down column B (just GTH- over and over) and column C (just 12- over and over) as far as you want to go down.

You are now ready to use a Concatenate formula.

f* is the functions tool, Concatenate can be found by going there and selecting All, from the top scroll down bar. Scroll down as it is in alphabetical order.

Make sure you are in cell A1.

Once the formula is ready to start, you simply click Cell B, then Tab, click Cell C, then Tab, click Cell D, now hit OK.

Your first formula would read GTH-12-001.

Copy that down as far as you like and what would have taken you hours to type has been done in less than a minute.


I would select the entire row A, right click, select Copy, right click again and select Paste Special, now select Values, now OK.

You have just removed the formula and can now remove columns B, C and D.

A little history lesson for Concatenate. Such a useful tool, it can be used as many cells as you like. Really is a life saver.

Hope that helps answer your question, feel free to ask away.