Re: Separate Text - Formula

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Ken Wright (ken.wright_at_NOSPAMntlworld.com)
Date: 06/03/04 

Date: Thu, 3 Jun 2004 22:22:55 +0100

Can't you use text To Columns and use comma as the separator as per your
example. This gets City in the first column and State / Zip in the second. Now
use text To Columns on Column 2 and use Space as the separator. 

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
              Sys Spec - Win XP Pro /  XL 97/00/02/03
----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------
"Da" <anonymous@discussions.microsoft.com> wrote in message
news:17eec01c449ad$86b400d0$a501280a@phx.gbl...
> Please help me with the formula to separate text. In the
> formula below, it works if the city is ONE WORD. However,
> when the city has two words, i.e., El Paso, the 'LEFT' and
> the 'MID' formulas does not work.
>
> I can't use the Data > Text to Columns feature because if
> I check space, it will separate the name of the city (if
> two names). If I do not check the space, the State and the
> Zip Code will be in one cell.
>
> Please HELP!
>
>
> Dallas, TX 11111
>
> City: =LEFT(A1,FIND(" ",A1,1)-2)
> State: =MID(A1,FIND(" ",A1)+1,2)
> Zip Code: =RIGHT(A1,5)
>
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.692 / Virus Database: 453 - Release Date: 28/05/2004
Quantcast