Complex =mid formula
 

I have a field which is lets say 13 ABC Street

Now I'm trying to isolate the street name from the street number. I
tried this:

=mid(A1,SEARCH(" ",A1,1,len(a1)-search(" ",a1,1)

But it doesen't let me. What this does is searches for the first space
and returns a value of 3, so this tells the =mid to start from the third
character. Then I do a LEN to get the total number of characters then
SUBTRACTED by another search which should give me:

=mid(a1,3,10)


--
foff
------------------------------------------------------------------------
foff's Profile: http://www.excelforum.com/member.php...o&userid=26272
View this thread: http://www.excelforum.com/showthread...hreadid=395640

funniest thing, excel asked me to correct it and offered this:

=MID(A1,SEARCH(" ",A1,1),LEN(A1)-SEARCH(" ",A1,1))

Which is just a bracket in the end could've sworn I tried it.

EDIT:

Ok this one does it:

=MID(A1,SEARCH(" ",A1,1)+1,LEN(A1)-SEARCH(" ",A1,1))


--
foff
------------------------------------------------------------------------
foff's Profile: http://www.excelforum.com/member.php...o&userid=26272
View this thread: http://www.excelforum.com/showthread...hreadid=395640

Hi foff,

If you only need to sparate the number from the rest of the text, you could
use these formulae:

=LEFT(A1,FIND(" ",A1)-1)
=TRIM(MID(A1,FIND(" ",A1),LEN(A1)))

Reagards,
KL


"foff" wrote in message
...

funniest thing, excel asked me to correct it and offered this:

=MID(A1,SEARCH(" ",A1,1),LEN(A1)-SEARCH(" ",A1,1))

Which is just a bracket in the end could've sworn I tried it.

EDIT:

Ok this one does it:

=MID(A1,SEARCH(" ",A1,1)+1,LEN(A1)-SEARCH(" ",A1,1))


--
foff
------------------------------------------------------------------------
foff's Profile:
http://www.excelforum.com/member.php...o&userid=26272
View this thread: http://www.excelforum.com/showthread...hreadid=395640