split cell contents (Excel 2003 SP3)

steveh
SilverLounger
Posts: 1952
Joined: 26 Jan 2010, 12:46
Location: Nr. Heathrow Airport

split cell contents (Excel 2003 SP3)

Post by steveh »

Good afternoon

I have a list of City codes and city names, for example

AAL - Aalborg, Denmark Aalborg
AES - Aalesund, Norway Vigra
AAR - Aarhus, Denmark Tirstrup
YXX - Abbotsford, Canada - Abbotsford Airport
ABZ - Aberdeen, Scotland, United Kingdom Dyce
ABR - Aberdeen, SD, USA Aberdeen Regional Airport

That is 3 alphas space-space in all instances

I would like if possible a formula that I could run in one column that says = the 3 alphas that I could then drag down the list (AAL, AES etc), and then another for another column which would =igonore the first 6 pieces of data xxx(space)-(space) and then display the name that follows it and drag it down (Aalborg, Denmark Aalborg, Aalesund, Norway Vigra etc)

The things I have Googled for do not seem to fit the bill for this
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin

User avatar
SteveH2508
NewLounger
Posts: 18
Joined: 24 Feb 2010, 20:38
Location: Chelsea, Greater London, England

Re: split cell contents (Excel 2003 SP£)

Post by SteveH2508 »

Is this a one-shot deal or will it need to be used over again?

If it is a one-time clean up then the Text to Columns using the hyphen as a separator would do it. (You may need to Trim the entries afterwards for blank spces)

=LEFT(A1,3) will get you the three characters on the left

=MID(A1,6,255) will get the other stuff
Access/Excel Dabbler
Windows XP SP3/Windows 7/O2K/O2007

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: split cell contents (Excel 2003 SP£)

Post by HansV »

Let's say the list is in column A, starting in A2.
Enter the following formula in B2:

=LEFT(A2,3)

Enter the following formula in C2:

=MID(A2,7,200)

The 200 is an arbitrary number greater than the length of the longest text you expect.

Select B2:C2 and use the fill handle to fill down as far as needed.
Best wishes,
Hans

steveh
SilverLounger
Posts: 1952
Joined: 26 Jan 2010, 12:46
Location: Nr. Heathrow Airport

Solved - Re: split cell contents (Excel 2003 SP3)

Post by steveh »

Thanks Guys

Nice to see you about Steve (Just realised I knicked your moniker!!)
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: Solved - Re: split cell contents (Excel 2003 SP3)

Post by StuartR »

steveh wrote:...(Just realised I knicked your moniker!!)
:cop: Identity theft alert :cop:

Would you like to purchase a very expensive identify theft alerting software with an insurance policy that is guaranteed not to pay out for any conceivable eventuality.

:whisper: Sorry, the cynic took over my typing fingers for a moment there
StuartR


steveh
SilverLounger
Posts: 1952
Joined: 26 Jan 2010, 12:46
Location: Nr. Heathrow Airport

Re: split cell contents (Excel 2003 SP3)

Post by steveh »

:rofl:

I hadn't thought of that
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin