Microsoft Excel Split Function Separate Column

user_18273386 0
Viewed 41

Hi I would like to split the range of data from cells A to range of cells B

ex:

table

I need the data which is located at the column DATA , split it with double space to column RESULT / split the right data to column result … which is I still try to figure it how to make it works as I had tried split function , delimiter it still cannot produce the result above … hope anyone can help ….

excel
1 Answers

There is many different ways to achieve that.

I assume that your table starts from A1 and that there is two whitespaces between letters in the column DATA.

By using standard Excel functions:

=TRIM(MID(A2,FIND("  ",A2)+1,256))

Formula to put in B2 and pull all the way down

By using new Excel 365 function TEXTAFTER() :

=TEXTAFTER(A2, " ", -1) or =TEXTAFTER(A1:A4," ") as JvdV suggested

This function is available for only Office 365 Insider members.

By using Text.AfterDelimiter() in PowerQuery:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "RESULT", each Text.AfterDelimiter([DATA], "  "))
in
    #"Added Custom"

enter image description here

EDIT:

By choosing the Power Query approach, you can create a VBA button that will refresh the query whenever you want.

Sub Macro1()

    ActiveWorkbook.Connections("Query - Table1").Refresh

End Sub

enter image description here

user_196034190
Related