splitting cells in excel

splitting cells in excel

Didn't find your answer?

I have a list in excel of first and last names currently in one cell. To import into another program i need them in seperate cells. I know it can be done but just can't remember how.

Please help.
Ian Stacey

Replies (10)

Please login or register to join the discussion.

avatar
By AnonymousUser
08th Oct 2002 02:15

De-Foley-ated!
. .. and I thought it was male pattern baldness ...

I look forward to Peter's tutorial: please take note and act accountingweb supremos.

I simply adapted some ASP code that I had to write a while back, because VB is so appallingly dreadful at string parsing -- all of this can be done with complete ease in other languages, even JavaScript.

I do hope Bill Gates reads this site!

Thanks (0)
avatar
By AnonymousUser
08th Oct 2002 16:47

Amazed
I am amazed at the response the thread has received - after receiving such a speedy inital reply, resolving my particular problem i thought that would be it. That it has so developed is great and i hope it encourages others with what may be a relatively simple query to post as who knows how the thread will grow, or what else might come out of it, and who else may benefit from the wealth of knowledge freely offered in the feedback.

I would whole heartedly endorse the efforts a regular few put in and once again thank them all.

Thanks (0)
avatar
By AnonymousUser
08th Oct 2002 19:32

I too am amazed!

Ian asked what in my opinion was a very straight forward one-off situation. This has now received some 740 hits so far and a tutorial with another 665 hits (unless the hits are linked to this message) and 15 comments (excluding this).

This only shows that IT questions has wider appeal than accounting and/or tax. Does this explain why The Times or the Telegraph don't have dedicated Finance Jobs Section?

It reminds of the Accountancy Magazine (ICAEW monthly magazine) which in the 1980s was subscribed mainly for its cartoons!. So the editor requested more cartoons and less technical articles!.

Thanks (0)
avatar
By AnonymousUser
07th Oct 2002 16:57

De-Foleyation of formula - Power unleashed
The cunning combination of SIX Excel functions by Bob Foley is worthy of further examination as each function is useful in it's own right as well as in other combinations to complete the answer to Frank Pilcher's question ie how to identify each element in a combination of Title, Initials and Surname or First Name and Surname or other scenarios.

Basically we need to locate the spaces between each element and then return the text
to the LEFT of the first space, MID way between the two spaces and RIGHT of the second space.

The formula is easier to understand if it is broken down

I have a fully illustrated Tutorial, which explains exactly how to do this BUT it won't fit in the Comments Box. I will send it to John Stokdyk, who may publish it or email me [email protected]

Thanks (0)
avatar
By AnonymousUser
08th Oct 2002 13:11

Here is the tutorial!
Frank, Jay, Bob,

The tutorial as promised by Peter is now online at:

https://www.accountingweb.co.uk/item/92615/448

I am particularly interested in Frank's comments on how popular all things Excel related are on AccountingWEB. We have plans to develop a new section of the site around this theme - more details to follow.

In the meantime, this thread and the tutorial have made it into today's Insider newswire - keep an eye on your inboxes, it should be hitting them any minute now!

Thanks

Richard

Richard Norton
Community Manager
AccountingWEB

Thanks (0)
avatar
By AnonymousUser
04th Oct 2002 23:14

Ian's posting has attracted 397 hits so far!

I am surprised that this message has received 397 hits so far (Friday, 4th Oct 2002 @ 23:06). Thanks to Bob Foley for keeping up the interest alive.

Frank Pilcher's problem can be solved using the same technique of Text to Columns with some "Data Cleansing" to be done. Such crude methods are only good for one time data import and so invariably some cleansing has to be done manually.

For example, I have the following data in my Excel Cells A1:A5

Mr F Pilcher
Prof Bob Foley
Ms P Booth
Gary Mackley-Smith
Mrs A. Robinson


When you follow Text to Columns you get three columns as follows:

Mr F Pilcher
Prof Bob Foley
Ms P Booth
Gary Mackley-Smith
Mrs A. Robinson


Now you need to move Mackley-Smith from second column to third and this can only be done manually.

Hope this gives you the idea.

Best regards,


Thanks (0)
avatar
By AnonymousUser
08th Oct 2002 15:01

Small suggestion
The tutorial is lovely, but I'd suggest it needs a stronger introduction to show casual browsers why it's valuable (several of the comments have already missed the point that Text to Columns is not infallible, especially if the original data was entered by a lunatic ... or it isn't in one column). How about this:

The thread developed into a discussion on how you can cope with a situation where you have several distinct data items entered in a single cell -- and entered in such an inconsistent way (extra spaces, different orders, etc) that the Text to Columns option doesn't help.

You can of course have strong words with the person who entered the data in the first place, but that doesn't solve the problem. And in any case, it may be that the data has just ended up in such a mess when extracted from some other program.

Thanks (0)
avatar
By AnonymousUser
03rd Oct 2002 22:02

Try TEXT to COLUMNS

Ian,

Have you tried Text to Column function in excel.

You get this by going to DATA/TEXT TO COLUMNS and follow the instructions on the screen. Forexample select on the screen:

Select DELIMITED
Click Next button
Select Space
Click Next button
Change the destination cell e,g, C12 (make sure you don't have anything in C12 C13 and C14)
Click FINNISH button


Hope this helps.

Thanks (0)
avatar
By AnonymousUser
04th Oct 2002 10:15

Thanks
Jay, thanks for that - so simple when you know how. Cheers. Ian

Thanks (0)
aw_logo_2019
By Accounting WEB
28th Sep 2005 13:43

Simple solution using Word
There are many solutions to this problem. Here is yet another one (or maybe not, I haven't looked through all of the previous answers).

I would simply copy everything and paste it into an empty word document.

Important!: When pasting choose "paste special" and "Unformatted text"

Then do a "find and replace" in Word (press ctrl+h). In the find box simply press the space button one step (or write a comma , ) if the names are separated with a space (or a comma). In the replace box write "^t" (without " "). The press the "replace all"-button. ^t is the sign for tab.

Now copy and paste everything into an excel sheet (preferably a new sheet). This time just paste as you normally do.

Now you will find that names has been separated as you wanted.

Thanks (0)