Nice one Ken!!

I had been playing with trying to get Char(10) into the Other delimiter
field, and it wouldn't work.
Hadn't thought about using Alt+0010

--
Regards

Roger Govier


"Ken Wright" wrote in message
...
On a COPY of your data:-

Select all your data and do Edit / Replace, and whilst in the 'Find
what'
section, on your NUMBER PAD section of your keyboard and whilst
holding down
the ALT key, type 0010 and then let go of the ALT Key. Now in the
'replace
with' section, put something like [ or % or anything that is not
likely to
appear in your data otherwise.

Now with your data selected use Data / text To Columns / Delimited,
and use
whatever character you used to replace 0010

OR, you can actually just use the ALT+0010 in the Delimited Option of
Data /
text To columns, which is probably easier :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------
It's easier to beg forgiveness than ask permission :-)



"Ron Rosenfeld" wrote:

On Tue, 31 Oct 2006 03:09:02 -0800, Danny boy <Danny
wrote:

Hi everyone,

Before i test the laws of gravity with my computer i thought i'd
post here
first. I am hopelessley trying to split the contents of a single
cell into
one (or more) columns. For example in A1 i have 3 'development
needs':

health and safety
management
fire training

i need to split the contents of this cell so each development need
transfers
into the adjacent cells, for example

health and safety stays in A1
management transfers to B1
fire training transfers to C1

i have tried texting to colums but i keep getting stuck as if i have
space
as a common delimiter it breaks health and safety (for example) into
3
columns! I can't do fixed width either as i have around 1000 rows of
data so
it may get messy.

Any takers for this? If i have to write code or something please be
gentle
as i have no idea about excel, i usually use SPSS in my job so this
is quite
new to me.

Thanks in advance!

Are the three development needs really on separate lines within the
same cell?

If so, you could use one of the following approaches.

1. Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

Then use the following:

B1: =REGEX.MID($A1,".*",COLUMNS($A:A))

Copy/drag this formula to D1.

That gives you the result in B1:D1

If you need it in A1:C1, then

select B1:D1
Edit/Copy
Edit/Paste Special Values
Select Column A and delete it.

2. (If you can't download and install the add-in):

B1: =LEFT(A1,FIND(CHAR(10),A1)-1)

C1:

=MID(A1,FIND(CHAR(10),A1)+1,
FIND(CHAR(10),A1,FIND(CHAR(
10),A1)+1)-FIND(CHAR(10),A1)-1)

D1:

=MID(A1,FIND(CHAR(1),SUBSTITUTE(
A1,CHAR(10),CHAR(1),2))+1,255)


--ron