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

martawrob

New Member
Joined
Jul 4, 2017
Messages
1
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:nostreet name
13 a, stag view
flat 3, hilton street
apartment 5, willow field

<tbody>
</tbody>
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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:
Upvote 0
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

:LOL:
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top