The old Google Groups will be going away soon, but your browser is incompatible with the new version.
This is a Usenet group - learn more
Message from discussion split street address did not work - help.xls (0/1)
The group you are posting to is a Usenet group. Messages posted to this group will make your email address visible to anyone on the Internet.
Your reply message has not been sent.
Your post was successful

From:
To:
Cc:
Followup To:
 Add Cc | Add Followup-to | Edit Subject
Subject:
 Validation: For verification purposes please type the characters you see in the picture below or the numbers you hear by clicking the accessibility icon.

More options Feb 17 2002, 9:32 am
Newsgroups: microsoft.public.excel.misc
From: "Peo Sjoblom" <gree...@lnubb.pbz>
Date: Sun, 17 Feb 2002 09:29:43 -0500
Local: Sun, Feb 17 2002 9:29 am
Subject: Re: split street address did not work - help.xls (0/1)
Hi Jon,

if that's the case then this should work

=LEFT(TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," "))),SEARCH(" ",TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," "))))-1)

for street numbers and

=RIGHT(TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," "))),LEN(TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," "))))-SEARCH("
",TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))))

for the names

--
Regards,

Peo Sjoblom

ROT13  email

"Crash" <joncr...@NOTrogers.com> wrote in message news:3C6F695B.36382FA1@NOTrogers.com...
> >copied into the spreadsheet from the table on our web page.

> There you go, I will bet you that the spaces aren't spaces (20hex, 32dec)
> their No-Break spaces (A0hex, 160dec).

> Your can check this out with

> =CODE(MID(A1,space position,1))

> 32 is a space
> 160 no-break space

> Jon

You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.

 Create a group - Google Groups - Google Home - Terms of Service - Privacy Policy ©2013 Google