Walt

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.

__________________
Calmer than you are...

Nepenthes

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.

dlish

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.

__________________
An injustice anywhere, is an injustice everywhere

I always sign my facebook comments with ()()===========(}. Does that make me gay?
- Filthy

Walt

Awesome! Thanks much!

I will definitely check that out. Thanks!

__________________
Calmer than you are...

amonkie

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.

__________________
Calling from deep in the heart, from where the eyes can't see and the ears can't hear, from where the mountain trails end and only love can go... ~~~ Three Rivers Hare Krishna

Daniel_

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).

__________________

╔═════════════════════════════════════════╗
Overhead, the Albatross hangs motionless upon the air,
And deep beneath the rolling waves,
In labyrinths of Coral Caves,
The Echo of a distant time
Comes willowing across the sand;
And everthing is Green and Submarine
╚═════════════════════════════════════════╝

GreyWolf

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).

__________________
The secret to great marksmanship is deciding what the target was AFTER you've shot.

Xazy

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

__________________
Now, a clever man would put the poison into his own goblet, because he would know that only a great fool would reach for what he was given. I am not a great fool, so I can clearly not choose the wine in front of you. But you must have known I was not a great fool, you would have counted on it, so I can clearly not choose the wine in front of me.

Jinn

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.

__________________
"I'm typing on a computer of science, which is being sent by science wires to a little science server where you can access it. I'm not typing on a computer of philosophy or religion or whatever other thing you think can be used to understand the universe because they're a poor substitute in the role of understanding the universe which exists independent from ourselves." - Willravel

Strange Famous

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))

_

__________________
"Do not tell lies, and do not do what you hate,
for all things are plain in the sight of Heaven. For nothing
hidden will not become manifest, and nothing covered will remain
without being uncovered."

The Gospel of Thomas

Daniel_

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

__________________

╔═════════════════════════════════════════╗
Overhead, the Albatross hangs motionless upon the air,
And deep beneath the rolling waves,
In labyrinths of Coral Caves,
The Echo of a distant time
Comes willowing across the sand;
And everthing is Green and Submarine
╚═════════════════════════════════════════╝