View original (French)

Extract street number and name

Solved
lalose Posted messages 2 Status Membre -  
lalose Posted messages 2 Status Membre -
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 réponse

Réponse 1 / 1
jee pee Posted messages 9403 Registration date   Status Modérateur Last intervention   9 948
 
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
lalose Posted messages 2 Status Membre
 
That's exactly what I was looking for!!
Thank you so much ;-)
0