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!

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

Hi

Assuming that there are line feeds in your data, which would be
represented by the Character Char(10), then the following seems to work.
I started with my data to be split in cell A4 - adjust accordingly
In cell B4
=LEFT(A4,FIND(CHAR(10),A4)-1)
in cell C4
=MID(A4,LEN(B4)+2,(FIND(CHAR(10),A4,LEN(B4)+2)-(LEN(B4)+2)))
in cell D4
=RIGHT(A4,LEN(A4)-FIND(CHAR(10),A4,LEN(B4)+LEN(C4)+2))

--
Regards

Roger Govier


"Danny boy" <Danny wrote in message
...
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!

Hi Ron,

Thanks for your quick response. We are not allowed at work to download
programs onto our machines.

To confirm the three (sometimes more, sometimes less) development needs are
all within one cell howver for some reason they have loads of pspaces between
each one for example:

health and safety management fire training

instead of just being:

health and safety management fire training

I copied and pasted your formulas but i received a '#VALUE!' message when i
pressed Enter. The only way i can think of doing this is to enter a comma or
other symbol manually after each development need but as stated earlier i
have many rows of data.

I'm so fed up with this problem, if you could help further Ron i'd be very
grateful.

regards

Hi Roger,

i have copied the B4 formula into B4 (after moving my data into A4) and
pressed enter and i still get the #VALUE! message. I can only assume that i
don't have 'line feeds' although i have no idea what they are to be frank.

if the formula did work i would need to apply it to lots of data as well.

Kind regards

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

Obviously you would this in the 'Other' section of the delimited dialog box.

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

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

Given your revised requirements (that there are lots of spaces between the
titles), it shouldn't be too difficult to split up your cell properly. You also
need to address the #Value error you are getting. It must mean you are not
copying the formula properly. So let's keep it simple and deal with one at a
time. First, try to pick off the left-most development need, by looking for the
first occurrence of two spaces together. Try:

=left(a1,find(" ",a1)-1)

Note there are two spaces between the quotes.

If this works, post back and we can show you how to pick off the rest, or work
with the formulas Ron has given you, replacing Char(10) with " ".

--
Regards,
Fred


"Danny boy" wrote in message
...
Hi Ron,

Thanks for your quick response. We are not allowed at work to download
programs onto our machines.

To confirm the three (sometimes more, sometimes less) development needs are
all within one cell howver for some reason they have loads of pspaces between
each one for example:

health and safety management fire training

instead of just being:

health and safety management fire training

I copied and pasted your formulas but i received a '#VALUE!' message when i
pressed Enter. The only way i can think of doing this is to enter a comma or
other symbol manually after each development need but as stated earlier i
have many rows of data.

I'm so fed up with this problem, if you could help further Ron i'd be very
grateful.

regards

On Tue, 31 Oct 2006 03:55:01 -0800, Danny boy
wrote:

Hi Ron,

Thanks for your quick response. We are not allowed at work to download
programs onto our machines.

To confirm the three (sometimes more, sometimes less) development needs are
all within one cell howver for some reason they have loads of pspaces between
each one for example:

health and safety management fire training

instead of just being:

health and safety management fire training

I copied and pasted your formulas but i received a '#VALUE!' message when i
pressed Enter. The only way i can think of doing this is to enter a comma or
other symbol manually after each development need but as stated earlier i
have many rows of data.

I'm so fed up with this problem, if you could help further Ron i'd be very
grateful.

regards


You will need to supply accurate information in order to obtain help.

In your first posting, you displayed the entry as being on three separate lines
within one cell. I wrote that my solution assumed that; therefore it is no
surprise that my solution gives an error result when applied to an entry that
does not meet the specifications.

"Loads of Spaces" is not very specific.

You will need to identify some feature that allows a computer other than your
brain to differentiate the different headings.

For example, if within a heading there is never more than one space; and
between headings there are always more than one space; then a function based
solution can be derived. If you cannot come up with separation rules, you will
need to manually input something that indicates a separation.

In this post you imply that there may be a variable number of headings in the
cell that need to be split out. If this is the case, then what is the maximum
and minimum you wish to allow for.

In any event, the following will work if the criteria for separating headings
is either multiple spaces; or multiple lines within the cell.

Because I'm lazy :-)) (and you can't download add-ins) I will use similar
formulas to what I recommended, but provide a VBA routine to interpret them.

To enter the VBA routine:

<alt<F11 opens the VB Editor.
Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens.

**Very Important**

Select Tools/References and, from the drop down list (which may be quite
lengthy) locate and select "Microsoft VBScript Regular Expressions 5.5"

*****

To use this routine,

B1: =remid($A1,"\S.*?(?=(\s\s)|$)",COLUMNS($A:A))

Select B1 and copy/drag to the right for as many columns as you might possibly
have headers. (It will return blanks if there is no value.

In the formula, the COLUMNS function is merely a counter, generating an index
number to extract either the 1st, 2nd or nth instance matching the Regular
Expression.

The Regular Expression: "\S.*?(?=(\s\s)|$)"

translates as
Find a series of characters which
starts with a non-Space character and
ends with either 2 spaces or an end of line.
Don't return the terminating characters, though.


Let me know how this works out.


=============================================
Option Explicit

Function REMid(str As String, Pattern As String, _
Optional Index As Variant = 1, _
Optional CaseSensitive As Boolean = True, _
Optional Multiline As Boolean = True) _
As Variant 'Variant as value may be string or array

Dim objRegExp As RegExp
Dim objMatch As Match
Dim colMatches As MatchCollection

Dim i As Long 'counter
Dim t() As String 'container for array results

' Create a regular expression object.
Set objRegExp = New RegExp

'Set the pattern by using the Pattern property.
objRegExp.Pattern = Pattern

' Set Case Insensitivity.
objRegExp.IgnoreCase = Not CaseSensitive

'Set global applicability.
objRegExp.Global = True

'Set multline
objRegExp.Multiline = Multiline

'Test whether the String can be compared.
If (objRegExp.Test(str) = True) Then

'Get the matches.
Set colMatches = objRegExp.Execute(str) ' Execute search.

On Error Resume Next 'return null string if a colmatch index is non-existent
If IsArray(Index) Then
ReDim t(1 To UBound(Index))
For i = 1 To UBound(Index)
t(i) = colMatches(Index(i) - 1)
Next i
REMid = t()
Else
REMid = CStr(colMatches(Index - 1))
If IsEmpty(REMid) Then REMid = ""
End If
On Error GoTo 0 'reset error handler
Else
REMid = ""
End If
End Function
=======================================
--ron

Hi everyone,

i'm really sorry but i just don't get it, i really am a complete novice with
this. i'm very sorry for not providing clarity earlier. i have had some
success, i have managed to 'TRIM' my data removing all the spaces except
between words. now my data looks like this in A1:

Fire training Health and Safety First Aid

there is one space only between each word. is there anyway i can split out
so fire training stays in A1, Health and Safety goes to B1 and First Aid goes
to C1. i would then need to drag this formula down for A2, B2, C2, D2 etc

THANK YOU ALL SO MUCH for what you have provided so far, i'm trying to
understabnd but i struggle with codes, vba etc.



"Danny boy" wrote:

Hi Roger,

i have copied the B4 formula into B4 (after moving my data into A4) and
pressed enter and i still get the #VALUE! message. I can only assume that i
don't have 'line feeds' although i have no idea what they are to be frank.

if the formula did work i would need to apply it to lots of data as well.

Kind regards

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

On Tue, 31 Oct 2006 06:31:01 -0800, Danny boy
wrote:

Hi everyone,

i'm really sorry but i just don't get it, i really am a complete novice with
this. i'm very sorry for not providing clarity earlier. i have had some
success, i have managed to 'TRIM' my data removing all the spaces except
between words. now my data looks like this in A1:

Fire training Health and Safety First Aid

there is one space only between each word. is there anyway i can split out
so fire training stays in A1, Health and Safety goes to B1 and First Aid goes
to C1. i would then need to drag this formula down for A2, B2, C2, D2 etc

THANK YOU ALL SO MUCH for what you have provided so far, i'm trying to
understabnd but i struggle with codes, vba etc.




Using TRIM would make it impossible to use the solution I supplied. It would
probably make it impossible to use any of the other posted solutions, also,
given that your data is all on one line.




--ron