• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Separate Names and Numbers.

L

Lasantha

Member
Dear All,

Kindly look into the attached files. it contains patient names and account numbers in column A, I want to separate them into 2 columns. I have given a example. could you please give me a formula for this. thank you.

Lasantha.
 

Attachments

  • Book1.xlsx
    11.2 KB · Views: 10
you could use this formula as well and copy it down
In B3 =LEFT(A3,SEARCH("(",A3)-1)
in C3 =MID(A3,FIND("(",A3)+1,LEN(A3)-FIND("(",A3)-1)
 
An alternative solution is to use Power Query/Get and Transform
Here is the Mcode for your sample worksheet

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Patient/Acct", Splitter.SplitTextByDelimiter("(", QuoteStyle.Csv), {"Patient/Acct.1", "Patient/Acct.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Patient/Acct.1", type text}, {"Patient/Acct.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type", "Patient/Acct.2", Splitter.SplitTextByEachDelimiter({")"}, QuoteStyle.Csv, true), {"Patient/Acct.2.1", "Patient/Acct.2.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Patient/Acct.2.2"})
in
    #"Removed Columns"

If you are unfamiliar with Power Query, then look at this link
Power Query-How to use Mcode
 
You must log in or register to reply here.
Back
Top