Need help with Excel
 

I was hoping someone here could help me out. I am trying to organize a course list for a friend. I have about 800 courses that are listed like so in column A:

ANT 1210 Introduction to Anthropology.

I would like to separate the course abbreviation and number (the first 8 characters) in to column A and the course title in to column B for all 800. Is there a quick formula I can plug in to do this for me or am I stuck doing it manually?

Thanks.

Yes, there are a couple ways about it.

You can get the eight left characters in a cell by using the formula =left(cell with data,8).


You can also go to Data, Text-to-Columns to split the text into two columns using the fixed-width option.

thanks a pretty cool trick Nep!

theres a guy online called The Excel Addict who send out weekly newsletters to his subscribers with free tips for excel. You may want to consider joining his list if you want to brush up on your excel skills. Ive been a part of it for about a year now and ive picked up many cool tricks.

Awesome! Thanks much!

I will definitely check that out. Thanks!

I didn't know about the Excel Addict but I'm in excel every day for my job.

I usually prefer the Text to Columns approach as I export large amounts of raw data from our database, and sometimes the formatting is different each time. So rather than try to set up formulas, I can set the parameters to catch either by comma, or fixed width. These seem to work for 95% of the data formatting.

I tend to go the other way - because you can add new data and drag the formulae to extend the aproach. Most of my excel work is about solving problems that will come round again, and I have been bitten in the arse before by solving a problem the "quick" way today, only to have the same query come back later. I tend to find that (quick * 2) > (slow).

A more generic solution, if you're still looking for one is to use the FIND function in conjunction the LEN, RIGHT, and LEFT functions.

The formula LEFT(cell,(FIND(" ",cell)-1)) will give you all the characters to the left of the first space in a cell (note the space in quotations).

The formula RIGHT(cell,(LEN(cell)-FIND(" ",cell))) will give you all the characters to the right of the first space in a cell.

While more complicated, these will work if the original list is not a fixed length (Subject codes may have different numbers of letters, or the course numbers may have more or fewer digits).

A very good forum that offers excel help for free, is Excel Help Forum

For anything that requires sophisticated parsing that I know the rules for, I'll paste the raw data into TextPad and then use regular expression Search and Replace to move it around. Then I'll just replace \w or empty space or number or whatever I need to with a delimiter (like comma) and save it as a csv. Then it opens in Excel NP. It's largely a carryover from 2003, where they didn't have Text to Column. 2007+'s T2C is awesome.

or else just use:

=left(A1,8)
=mid (A1,9,50)

It basically means:
take the first 8 characters on the left
take the 50 characters in the middle, starting from the 9th one in)

If you have fixed characters, thats sometimes easier than using text to columns, as you can resort if

ie - if you wanted to reverse it so the name was first and the course code last you could go:

=(mid(A1,9.50)) & (left(A1,8))

_

I never understood why there is a CONCATENATE and "&". Anyone know? They work the same way as far as I can see.