Excel extracting text into columns

Question

Is there a way to extract data from a single cell and split it into columns by headers. For example we have in A1 cell text like this:

Name: John
Address: USA, New York
Age: 66

I want to split this text into columns with headers Name, Address, Age and extract data to the following columns. I'd be grateful for tips.

Which version of excel do you have? – Harun24hr Oct 30 '20 at 09:40 — Harun24hr, Oct 30 '20 at 09:40

score 3 · Accepted Answer · answered Oct 30 '20 at 09:57

3

This is little bit tricky but will work on all version of excel. As per below screenshot Put Name, Address, Age to B1,C1 & D1 cell then put below formula to B2 cell then drag down and right as needed.

=SUBSTITUTE(TRIM(MID(SUBSTITUTE($A2,CHAR(10),REPT(" ",100)),((COLUMN(A$2)-1)*100)+1,COLUMN($A$2)*100)),B1&": ","")

answered Oct 30 '20 at 09:57

Harun24hr

30,391
4
21
36

Nice idea + to stretch original strings and rejust them afterwards. Nevertheless rows 2 and following seem to *include* the prefixed strings "Name: ", "Address: " and "Age: " instead of substituting them with "". - FYI added a VBA array approach via filtering. – T.M. Oct 30 '20 at 18:03

JvdV · Answer 2 · 2020-10-30T09:58:18.387

2

If you wouldn't mind using formulas instead of VBA:

With Excel O365:

Formula in B2:

=TRANSPOSE(TRIM(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A2,":",CHAR(10)),CHAR(10),"</s><s>")&"</s></t>","//s[position() mod 2 = 0]")))

With Excel 2013 or higher, other than O365:

=INDEX(TRIM(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE($A2,":",CHAR(10)),CHAR(10),"</s><s>")&"</s></t>","//s[position() mod 2 = 0]")),COLUMN(A1))

And drag over and down...

edited Oct 30 '20 at 09:58

answered Oct 30 '20 at 09:45

JvdV

70,606
8
39
70

Any other way of doing this in Excel / Power Pivot/ Power BI? Unfortunately I don't have access to ver 0365. – glizzz Oct 30 '20 at 09:49
@WojciechUrjasz, for sure. Though I added an non O365 version of the `FILTERXML` route. – JvdV Oct 30 '20 at 09:59
1

Worth mentioning that JvDV posted an excellent overview of `FilterXML` XPath cases at [Extract substrings using FilterXML()](https://stackoverflow.com/questions/61837696/excel-extract-substrings-from-string-using-filterxml/61837697#61837697). FYI posted an alternative VBA approach. @JvdV – T.M. Oct 30 '20 at 18:06

score 2 · Answer 3 · answered Oct 30 '20 at 14:25

A VBA array approach

This late post in addition to the valid answers above demonstrates an array approach and a double splitting:

section [1] splits into lines via the vbLf delimiter (equalling Chr(10)),
section [2] restricts splitting to two parts (via ": ")

As it's not so widely known how to use the Split() function by limiting output to 2 tokens only as shown in section [2], have a look at the

Syntax

Split(expression, [ delimiter, [ limit, [ compare ]]])

Option Explicit

Sub SplitIntoTokens()
With Sheet1                                   ' << change to your project's sheet Code(Name)
    '[0] get string content
    Dim lastRow As Long
    lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
    Dim data As Variant
    data = .Range("A1:D" & lastRow).Value
    
    Dim i As Long
    For i = 2 To UBound(data)
        '[1] split into lines
        '    ~> Name: John|Address: USA, New York|Age: 66
        Dim lines: lines = Split(data(i, 1), vbLf)
        
        '[2] split into 2 parts only and take the 2nd one
        Dim ii As Long, tmp
        For ii = 0 To UBound(lines)
            lines(ii) = Split(lines(ii), ": ", 2)(1)    ' split via ": "-delimiter, 2nd part via index (1)
            data(i, ii + 2) = lines(ii)
        Next
        'Debug.Print Join(lines, "|") optional (display results in VB Editors Immediate Window
    Next
    
    '[3] write array results back to sheet
    .Range("A1").Resize(UBound(data), 4) = data             ' write data
    .Range("A1:D1") = Split("Data,Name,Address,Age", ",")   ' write header (if not existant)
End With
End Sub

Excel extracting text into columns

3 Answers3