I have work to do in excel and need help. I just KNOW there is a formula that does this.

I have 2 spreadsheets at work that I am merging into word to print names and addresses onto envelopes. My first spreadsheet works fine b/c i have names in column a, address in column b, zip code in column c and so forth. So when I merge into word, it is easy to have word recognize info in certain columns and align them accordingly to designated spots on my envelope template.

In my second spreadsheet, the bishes who put it together were lazy and threw everything into column a. Is there a formula that can split up the data according to how much space is between each word and then put it into apprpropriate columns?

I'm looking for it to read first words separated by one space as one set of info to be placed in column a; ex. if spaces >=5 then next info in cell B, if 2 caps followed by 5#and or 5#-4# then 2 caps in cell E and # in cell F...

I got this assignment yesterday and it is due today at 9pm! Excel gurus please help!

 

How many entries are there?

 

Start typing now.

 

I'm no pro, but I'm sure there's someone here that can help you on this. I believe the term you're looking for is delimiters???

 

what is a delimiter?

there are about 1400 entries and it takes me about an hour per 50. So do the math and i have enough time to manually do 500 before it is due...big trouble for me!

I was really hoping a formula could do this so I wouldn't have to do it by hand, b/c there';s no way I can finish in time anyway!

 

Assume name is "John Smith"

To get first name: =LEFT(A1,IF(ISERROR(FIND(" ",A1,1)),LEN(A1),FIND(" ",A1,1)-1))

To get last name: =TRIM(IF(ISERROR(FIND(" ",A1,1)),A1,MID(A1,FIND(" ",A1,1)+1,
IF(ISERROR(FIND(" ",A1,FIND(",",A1,1)+2)),LEN(A1),
FIND(" ",A1,FIND(" ",A1,1)+2))-FIND(" ",A1,1))))

The second formula will only work if it's just first and last name (no middle initials)

 

try 'text to columns' under the data menu...allows you to break a column up similar to your options when opening a text file

 

NSXNEXT,

you sir, are THE SHIT! HOT DAMN!

 

If you have commas between the first and last name just replace the " " throughout the forumula.

 

also I hope when it comes to word you are just doing a mail merge for envelopes/labels and using the Excel spreadsheet as the data source. Otherwise you are doing too much work.

 

Nice edit.

 

Wow man...impressive...I'm gonna give that a shot and see what happens.

Middle initials are not necessary, so if they are in there, will it just omit them? Also can these formulas be applied to the street address and the city state and zip separately?

I should probably know a little more about how the formulas work if I want to alter them, huh? what is iserror? what does excel look for with that term?

Thanx so much for posting so quickly...I really appreciate you guys saving my ass at work!!

adamnj, is there another way to do it that would create more work?

 

This I would have to wholeheartedly agree...I didn't expect such a precise comment so soon!

 

I think I would have copied it into word. Replaced all spaces with a common and a space or with a tab and then imported it into excel with common or tab delineated columns...

 

I use Excel ALL THE TIME so I've kept a nice list of formulas to refer to.

 

man u guys really know your excel...other than entering data, this is the most advanced excel work I have ever done. So I hope you don't mind me asking...what is a tab delinieated column?

btw: I posted this at excelforum (which I figure would get some helpful responses...and once again I come back to AZ as this seems to be the only place where there is someone who ha an answer to every question you may have...) this site does rock, and it's because of the members!

NSXNEXT, would it be too much trouble to get a copy of those formulas you have laying around? I'm thinking you may have stuff there that will save my ass next week too!

 

If you look through excel help, or the list of forumlas in the formula window (down arrow next to autosum >> more functions), you can discover what you can do.

Delinieated (anything) means that 'something' (in this case a column) contains multiple piece of information which are broken up (delinieated) by something...be it a character like a space or comma. An alternative is 'Fixed Width', meaning you know that even though everything is in one 'something', at character position 10 starts a first name, at character position 35 starts the last name...etc

Both can be handled by 'text to columns' or opening a .txt file (importing).

 

NSXNEXT just blew my f#$ckn mind

 

does the formula list have all the formulas? so with fixed width, you can set it up to read a space break of say, 30 spaces between the last name and the address, and then move anything after that 30 spaces into another column? I'm assuming there is a similar formula to the name one that NSXNEXT poste above that will do this by recognizing the amounts of breaks between each piece of info you are trying to move?

 

Try it out...
Yes you set the breakpoints (it shows your actual data while you are setting them so it's easy to see what will happen). Each breakpoint causes data after it to be put into a new column. So when you take one column of data, and set 1 breakpoint at position 20, you are going to get one column of data up to #20, and another column with everything else.

 

NSXNEXT's formula is a compound(mega) formula. The beauty of Excel is that you can combine multiple formulas to perform complex functions.

You won't find that one in the formulas menu, except in its individual components.

BTW, nice coding NSXNEXT

 

OK...gonna give it a try...how do you set breakpoints? In the formula?

pmptx, so that compound formula he posted is moer than one formula? Is there a rule to follow when putting them together? Any certain characters you have to use to separate the formulas?

 

Any chance to get a copy of said list? My group uses excel all the time and always looking to add to our knowledge base.

 

also, what if the spaces between info in the cells are not the same from cell to cell? ex. ater "John Smith" there are 7 spaces until the beginning of the address. But in the cell below there are 12 spaces between "Mary Smith" and her address...Do you have to go in manually and adjust the breaks for the formula to read?

 

You will see how to set breakpoints when you open the 'text to columns' command / window. If you pick fixed width (if you data is such), a 'wizard' window shoudl come up, showing your data in a gird type window, the a ruler up top. It will try to automatically insert breakpoints. Breakpoints show up as vertical black lines, and there will be an arrow at the top where it meets the rulter...you can click and drag to move it, or double click to remove/add one.

Each time you see xxxxxx( <stuff here - aka operands> ), that is a seperate formula. xxxxxx is the operator aka the command/function, and that operator performs a function (something) on the operands. Each formula is setup to take a certain number of operands and they each tell the code something.

So 'left',' if', 'iserror', 'find', 'len' are all functions. As already mentioned you can put multiple functions together to work on a piece of data. In the formula above, 'if' is operating on ISERROR(FIND(" ",A1,1). Also FYI, when using a function, it doesn't matter the case (uppercase or lowercase) that you use.

 

To be more specific:

1.copy the data into word go to past special and paste as unformatted text
2. Replaced all spaces with a common and a space (ie each field that you want should have a common before it and after it - first name, last name, address, city, state, zip)
3. Save as a .txt file
4. In excel goto Data-Import External Data-Import Data...
5. Find your file and select it
6. A wizard box will come up select the delimited button hit next and then select only common delimited

This should do it....

I've never used the formula, so this of course is just one option...

 

Let's get your words straight first....Formulas are one thing, using 'text to columns' (delimited and fixed width) are not the same thing. When you use 'text to columns' and set a breakpoint, you are setting it for the whole column...it isn't a row by row thing.

I'd really have to see what you are talking about. Can you post a screenshot or something (black out the people's addresses/info though. If every row is not laid out in the same fashion, you probalby don't have fixed width data. And instead you should be using delimited (ie spaces).

 

yeah, definitely no fixed width data then. I can post an example of a screenshot, but how do Itake a screenshot? I can make an example file and post it, but how would I go about doing that?

 

screen shot = print scrn, then paste in paint or something, and then host it..

 

 

where can i get the pic hosted? can anyone do this for me?

 

imageshack

 

ok...hopefully i can get that up before time's up

 

how do i turn it into a gif or jpg from bmp?