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
1