formula jak odseparować z jednej kolumny adresowej numer domu od ulicy

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: formula jak odseparować z jednej kolumny adresowej numer domu od ulicy

  1. #1
    New Member
    Join Date
    Jul 2017
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question formula jak odseparować z jednej kolumny adresowej numer domu od ulicy

     
    proszę o podanie formuły tak aby z bazy danych dotyczących adresu odseparować od nazwy ulicy w osobnych kolumnach nr domu, mieszkania bądź apartamentu (czy można wpisać jedną uniwersalną formułę ab to zrobić czy 3 osobne, ponieważ recordy są pomieszane).

    Jak już to zostanie osiągnięte musze porównać i zaznaczyć takie same rekordy (zapisy) na dwóch excell sheet (też mam pytanie jak to najszybciej osiągnąć)

    będę bardzo wdzięczna za odpowiedź
    pozdrawiam Marta


    address: no street name
    13 a, stag view
    flat 3, hilton street
    apartment 5, willow field

  2. #2
    Board Regular
    Join Date
    Sep 2009
    Posts
    108
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: formula jak odseparować z jednej kolumny adresowej numer domu od ulicy

    Martawrob, I don't know how many native Polish speakers visit the forum and would be able to read and answer your question therefore. I have a very rudimentary understanding of Polish myself. Would it be possible to rephrase the question in English?

    My rudimentary understanding is that you want to retrieve the street name from the column where the address appears. Is this correct?
    Last edited by kalikj; Jul 4th, 2017 at 07:30 AM.

  3. #3
    Board Regular
    Join Date
    Sep 2009
    Posts
    108
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: formula jak odseparować z jednej kolumny adresowej numer domu od ulicy

    OK, if my understanding is correct and you have addresses in column A of your workbook per the examples you have shown (with a number then the street always after the ","), then you can get the number into one cell vs the street name in another by using the following (my example assumes that your first address ("13 a, stag view") is in cell A1:

    Type into B1 to retrieve number:
    =LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000)))) gives the result "13" in this example
    Type into C1 to retrieve street name: =RIGHT(A1,LEN(A1)-FIND(",",A1)) gives the result "stag view" in this example


  4. #4
    MrExcel MVP
    Int'l Moderator
    Greg Truby's Avatar
    Join Date
    Jun 2002
    Location
    39° 17' 15" N, -94° 40' 26" W
    Posts
    9,962
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: formula jak odseparować z jednej kolumny adresowej numer domu od ulicy

      
    Formula for house or apartment number:


    =LEFT(A1,FIND(",",A1)-1)


    Formula for street name (use Kalik's but wrap in a TRIM()):


    =TRIM(RIGHT(A1,LEN(A1)-FIND(",",A1)))


    Question: do you need a formula? You say this is coming from a database. If you are only importing data once in a while then look on your Data tab in the ribbon. There should be a tool called "text to columns" which will do this all at once. You might try it to see if that provides you with a suitable solution.

    GOOGLE TRANSLATE:

    Formuła numer domu lub mieszkania:


    = LEFT (A1, FIND (",", A1) -1)


    Wzór nazwy ulicy (użyj Kalika, ale zawiń w TRIM ()):


    = TRIM (PRAWO (A1, LEN (A1) -FIND (",", A1)))


    Pytanie: czy potrzebujesz wzoru? Mówisz, że pochodzi z bazy danych. Jeśli tylko raz importujesz dane, spójrz na kartę Dane na wstążce. Powinno być narzędzie zwane "tekstem do kolumn", które zrobi to na raz. Możesz spróbować sprawdzić, czy jest to odpowiednie rozwiązanie.
    Last edited by Greg Truby; Jul 6th, 2017 at 04:54 PM.
    Greg
    ………………………………………………
    Home: XL 2003, 2007, 2010, and 2013 on Windows 7
    Work: XL 2013, 2016 on Windows 10
    Please use CODE tags - especially for longer excerpts of code.

User Tag List

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
  •  

 

 
DMCA.com