excel question

listed in the excel spreadsheet attached in column a is  5 variables listed in one cell.
I'd like each variable broken up into 5 columns

Yellow is before
red is after
experts-exchange.xlsx
jamesmetcalf74Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

Select Column A

Goto Data / Data Tools / Text to Columns
choose delimited then Next
Tick Space and Treat consecutive delimiters as one and  Next
Choose Do not import column on the first Blck column and Finish

Regards
Copy-of-experts-exchange.xlsx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
you can also do that via a custom formula in which the cell's formula is linked to a function.

like to get the first cell's value from your combined value, try like:
=getValue($A1,1)

Open in new window


The module codes is shown as follows:
Function getValue(v As String, p As Integer) As String
    Dim Arr() As String
    Dim t As String
    If p < 0 Then Exit Function
    
    t = Trim(v)
    Arr = SplitEx(t, " ", , True)
    If p > UBound(Arr) + 1 Then
        getValue = ""
    Else
        getValue = Arr(p - 1)
    End If
End Function

Function SplitEx(InString As String, _
        Delimiter As String, _
        Optional GroupChar As String = vbNullString, _
        Optional IgnoreConsecutiveDelimiters As Boolean = False, _
        Optional Escape As String = vbNullString, _
        Optional RemoveEscape As Boolean = True, _
        Optional DeleteGroupCharacters As Boolean = False) As String()
'====================================================================================
' SplitEx
' By Chip Pearson, chip@cpearson.com , www.cpearson.com, www.cpearson.com/Excel/Split.aspx
'
' SplitEx is an extension to the standard VBA Split method. If all the optional
' parameters are omitted, SplitEx works just like Split.
'
' SplitEx provides the following advantages of the standard Split method:
'
'   GroupChar: This specifies a character that is used to delimit a range
'   in the input string within which any delimiter characters are to be ignored.
'   Most commonly, this is used to prevent SplitEx from splitting on a space
'   character that occurs within a quoted string. For example,
'       InputString = Hello "big world" from VB
'   If you set the GroupChar to Chr(34), the space within the quoted string
'   will not cause a split. The second element will be "big world" (with the quotes).
'
'   IgnoreConsecutiveDelimiters: This tells SplitEx to ignore consecutive delimiters
'   and treat them as a single delimiter. For example,
'       InputString = Hello|World||From VBA
'   SplitEx treats the consecutive delimiters || after 'World' as a single
'   delimter, so the string is split as if it were Hello|World|From VBA
'
'   Escape: This escapes a delimiter, so that it will not be used by Split. For
'   example,
'       InputString = Hello|Big\|World gets split into two components, not three.
'   The | delimiter that follows the \ escape character is not used by the split.
'   The second element is the text Big\|World
'
'   RemoveEscape: This causes the code to remove the escape character from the
'   final Split. For example, let Escape = '\' and InString is 'Hello|Big\|World'
'   If RemoveEscape is False, the second element is 'Big\|World'. If RemoveEscape
'   is True, the second element is 'Big|World', with the \ character removed.
'
'   DeleteGroupCharacters: If True, all instances of GroupChar are removed from
'   the output. Otherwse, the GroupChar remains.
'
' Results:
'   Normal: An array of strings that were split apart in the manner prescribed by
'       the various options.
'   If InString is an empty string, an uninitialized array is returned. Test this with
'       code like:
'           Dim SS() As String
'           SS = SplitEx(...)
'           If IsError(LBound(SS)) = True Then
'               ' uninitialized array. InString was empty
'           End If
'   If Delimiter is an empty string, the result is an array of one element that
'   contains InString.
'====================================================================================
Dim InGroup As Boolean
Dim Arr() As String
Dim N As Long
Dim InGroupReplace As String
Dim S As String
Dim Done As Boolean
Dim M As Long
Dim EscapeReplace As String

'
' In the input string is empty, return the
' unallocated array.
'
If InString = vbNullString Then
    SplitEx = Arr
    Exit Function
End If

'
' If the delimiter is empty, return a single
' element array containing the input string.
'
If Len(Delimiter) = 0 Then
    ReDim Arr(0 To 0)
    Arr(0) = InString
    Exit Function
End If

S = InString
N = 1
Done = False
'
' Find a character that is not used in InString. This character
' will be used to replace Delimiter when Delimiter occurs with
' a group of characters delimited by GroupChar.
Do Until Done
    If StrComp(Chr(N), Delimiter, vbBinaryCompare) <> 0 Then
        M = InStr(1, S, Chr(N), vbBinaryCompare)
        If M = 0 Then
            InGroupReplace = Chr(N)
            Done = True
        End If
    End If
    N = N + 1
Loop
InGroupReplace = Chr(N)
N = N + 1
Done = False
'
' Find a character not used in InString that we can
' use to mark an escaped delimter (an escaped delimiter
' is a delimiter than isn't used by the Split function).
If Escape <> vbNullString Then
    Do Until Done
        If StrComp(Chr(N), Escape, vbTextCompare) <> 0 Then
            M = InStr(1, S, Chr(N), vbBinaryCompare)
            If M = 0 Then
                EscapeReplace = Chr(N)
                Done = True
            End If
        End If
        N = N + 1
    Loop
End If
    
'
' Replace existing escaped delimiters with the EscapeReplace
' character.
If Escape <> vbNullString Then
    S = Replace(S, Escape & Delimiter, EscapeReplace)
End If
    
'
' If we are ignoring consecutive delimiters, replace
' consecutive delimiters with a single delimiter.
If IgnoreConsecutiveDelimiters = True Then
    N = InStr(1, S, Delimiter & Delimiter, vbBinaryCompare)
    Do Until N = 0
        S = Replace(S, Delimiter & Delimiter, Delimiter)
        N = InStr(1, S, Delimiter & Delimiter, vbBinaryCompare)
    Loop
End If

'
' Scan string and replace any delimter that occurs within
' a group sequence with InGroupReplace
If Len(GroupChar) > 0 Then
    For N = 1 To Len(S)
        If Mid(S, N, Len(GroupChar)) = GroupChar Then
            InGroup = Not InGroup
        End If
        If Mid(S, N, 1) = Delimiter Then
            If InGroup Then
                Mid(S, N, 1) = InGroupReplace
            End If
        End If
    Next N
End If

' do the split
Arr = Split(S, Delimiter)
' loop through the array and replace our special control
' characters with their original value.
For N = LBound(Arr) To UBound(Arr)
    Arr(N) = Replace(Arr(N), InGroupReplace, Delimiter)
    If DeleteGroupCharacters = True Then
        Arr(N) = Replace(Arr(N), GroupChar, vbNullString)
    End If
    If EscapeReplace <> vbNullString Then
        If RemoveEscape = True Then
            Arr(N) = Replace(Arr(N), EscapeReplace, Delimiter)
        Else
            Arr(N) = Replace(Arr(N), EscapeReplace, Escape & Delimiter)
        End If
    End If
Next N
SplitEx = Arr

End Function

Open in new window

An Enhanced Replacement For VBA's Split Function
http://www.cpearson.com/excel/Split.aspx
experts-exchange_b.xlsm
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.