+ Reply to Thread
Results 1 to 6 of 6

Split column

  1. 03-02-2023, 09:17 AM #1
    DarkSerge
    DarkSerge is offline
    Registered User
    Join Date
    03-02-2023
    Location
    Poland
    MS-Off Ver
    2013
    Posts
    1

    Split column

    Hey everyone. I need some help with my excel forumula. Actually I dont even know where to start with this. Can be solved in 2 ways

    1st way : I have 2 columns where one has the postal code and that postal code has to placed inside the second column in a specific place, after the full street name. Text inside the column has different lenght and numer of words. (Postal code has always the same lenght)

    2nd way: I want the City name to be deleted (wchich is different) but always is the last word in the column but thre are different lengths of the text in the column .


    Exact example in the attachment here to download

    Thank you in advance

    Nowy Microsoft Excel Worksheet (2).xlsx
    Last edited by DarkSerge; 03-02-2023 at 09:18 AM. Reason: typo
    Reply With QuoteRegister To Reply
  2. 03-02-2023, 10:10 AM #2
    Czeslaw
    Czeslaw is offline
    Forum Expert
    Join Date
    12-09-2014
    Location
    Trakai, Lithuania
    MS-Off Ver
    2016
    Posts
    1,416

    Re: Split column

    Power Query
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  3. 03-02-2023, 01:48 PM #3
    sandy666
    sandy666 is offline
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Split column

    another way with Power Query

    XL2013 require Power Query add-in

    Result
    S³oneczna 14, 41-203, Sosnowiec, Polska
    Al. RoŸdzieñskiego 210, 40-315, Katowice, Polska
    Gen. Stefana Grota Roweckiego 18A, 40-315, Pszczyna, Polska
    1 Maja 7, 43-500, Goleszów, Polska
    Rolna 14, 40-212, Katowice, Polska


    Edit: Unfortunately, this forum does not recognize Polish diacritics, but in Excel should be ok.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by sandy666; 03-02-2023 at 02:01 PM. Reason: update
    Reply With QuoteRegister To Reply
  4. 03-03-2023, 01:56 AM #4
    windknife
    windknife is online now
    Forum Expert
    Join Date
    04-14-2009
    Location
    Taiwan
    MS-Off Ver
    Excel 2016,2019,O365
    Posts
    2,913

    Re: Split column

    Try this,

    E2
    =LEFT(B3,FIND("^", SUBSTITUTE(B3," ","^", LEN(B3)-LEN(SUBSTITUTE(B3," ",""))))-1)&","&A3&MID(B3,FIND("^", SUBSTITUTE(B3," ","^", LEN(B3)-LEN(SUBSTITUTE(B3," ","")))),255)&", POLSKA"

    copied down.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 03-03-2023, 02:24 AM #5
    azumi
    azumi is offline
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 365
    Posts
    2,406

    Re: Split column

    Put in D3 anc copied down:

    =LEFT(B3,LOOKUP(2^15,FIND(" "," "&B3,ROW(INDIRECT("1:"&LEN(B3)))))-1)&", "&A3&" "&TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(B3,","," ")," ",REPT(" ",256)),256))&", POLSKA"
    Reply With QuoteRegister To Reply
  6. 03-03-2023, 03:21 AM #6
    kvsrinivasamurthy
    kvsrinivasamurthy is offline
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,710

    Re: Split column

    ARRAY formula in G3 copied down
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    To enter ARRAY formula
    Copy and paste the formula in cell
    Press F2
    Press Ctrl+Shift+Enter together
    Excel covers the formula with {}.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. power query m code split variable required to split data in one column instead of 2 col
    By JEAN1972 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-19-2022, 04:42 PM
  2. [SOLVED] Concatenate Text in Column B Split in Multiple Rows for Each Number in Column A
    By zaska in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-23-2020, 03:51 AM
  3. split excel sheet based on column filter with protected column
    By rishikrsaw in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-23-2019, 03:33 AM
  4. Split workbooks by a column value and then split to worksheets by another column value
    By jaredli in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-08-2019, 01:54 PM
  5. Need Macro code to split data split in 7 sheets based on variable rows in column A
    By Alija_21 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-31-2015, 08:03 AM
  6. How to split one column (Addresses, City, State, Zipcode, attn) into multiple column?
    By kittya in forum Excel General
    Replies: 3
    Last Post: 08-19-2014, 10:24 AM
  7. [SOLVED] Split One Column at Delimiter While Copying Contents of Second Column to Populate New Rows
    By medailSacrosanct in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 08-01-2012, 11:37 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1