View original (French)

Extract street number and name

Solved
lalose Posted messages 2 Status Member -  
lalose Posted messages 2 Status Member -
Hello everyone,
Some time ago, I found/used two Excel formulas that allowed me to split a cell containing an address into two cells (number & street).
Despite my many searches, I can't find them again...
So I'm reaching out to you, the community!

Here's what I have in my column A:
32 AVENUE DE TIERCE
432 CHEMIN DE FRETE
ALLEE TEST

What I want in column B:
"32"
"432"
"-empty-"

And in column C:
"AVENUE DE TIERCE"
"CHEMIN DE FRETE"
"ALLEE TEST"

Thank you all!

1 answer

  1.
    jee pee Posted messages 31886 Registration date   Status Moderator Last intervention   9 981
     
    Hello,

    Try 
    =IF(ISNUMBER(VALUE(MID(A1,1,1))),MID(A1,1,FIND(" ",A1))," ")

    and 
    =IF(ISNUMBER(VALUE(MID(A1,1,1))),MID(A1,FIND(" ",A1)+1,200),A1)

    the distinction being made on the first character, whether numeric or not

    --
            a stranger is a friend we haven't met yet.
    4
    1. lalose Posted messages 2 Status Member
       
      That's exactly what I was looking for!!
      Thank you so much ;-)
      0