Results 1 to 6 of 6

Thread: Split Text in to adress format

  1. #1
    New Member
    Join Date
    Feb 2016
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Split Text in to adress format

    Hi,

    Can some body help me to split the excel value.

    I have a list of address's in one excel and I want to split it into the normal address format.

    for eg. my current value is something like this,

    Abraham , XXX HOUSE, yyyy POST PLACE 689121

    I want to split it like thisin same excel cell

    Abraham ,
    XXX HOUSE,
    yyyy POST PLACE 689121

  2. #2
    Board Regular
    Join Date
    Oct 2007
    Location
    Kuwait
    Posts
    449
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Split Text in to adress format

    How about this formula considering your original example is in cell A1

    =SUBSTITUTE(A1,",",CHAR(10))

    Also, for the new cell, enable Warp Text

    Check the List of BB codes


  3. #3
    New Member
    Join Date
    Feb 2016
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Split Text in to adress format

    It's working but not coming in the exact format

  4. #4
    Board Regular
    Join Date
    Oct 2007
    Location
    Kuwait
    Posts
    449
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Split Text in to adress format

    If you are talking about the commas, try the below

    =SUBSTITUTE(A1,",",","&CHAR(10))

    EDIT : =SUBSTITUTE(A1,", ",","&CHAR(10))
    Last edited by mse330; Jun 1st, 2019 at 06:18 AM.

    Check the List of BB codes


  5. #5
    New Member
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    33
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Split Text in to adress format

    I would use Power Query for that.

  6. #6
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    5,006
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Split Text in to adress format

    Using Power Query, it can place the data in rows as shown below with two steps.

    Data Range
    A
    1
    Column1
    2
    Abraham
    3
    XXX HOUSE
    4
    yyyy POST PLACE 689121
    Mcode below
    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
        #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
        #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1", type text}})
    in
        #"Changed Type1"
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    How to insert Mcode to Power Query https://excel.solutions/2017/11/powe...te-code-video/


Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •