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
split cell contents (Excel 2003 SP3)
-
- SilverLounger
- Posts: 1952
- Joined: 26 Jan 2010, 12:46
- Location: Nr. Heathrow Airport
split cell contents (Excel 2003 SP3)
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
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
-
- NewLounger
- Posts: 18
- Joined: 24 Feb 2010, 20:38
- Location: Chelsea, Greater London, England
Re: split cell contents (Excel 2003 SP£)
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
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
Windows XP SP3/Windows 7/O2K/O2007
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: split cell contents (Excel 2003 SP£)
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.
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
Hans
-
- SilverLounger
- Posts: 1952
- Joined: 26 Jan 2010, 12:46
- Location: Nr. Heathrow Airport
Solved - Re: split cell contents (Excel 2003 SP3)
Thanks Guys
Nice to see you about Steve (Just realised I knicked your moniker!!)
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
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
-
- Administrator
- Posts: 12577
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Solved - Re: split cell contents (Excel 2003 SP3)
Identity theft alertsteveh wrote:...(Just realised I knicked your moniker!!)
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.
Sorry, the cynic took over my typing fingers for a moment there
StuartR
-
- SilverLounger
- Posts: 1952
- Joined: 26 Jan 2010, 12:46
- Location: Nr. Heathrow Airport
Re: split cell contents (Excel 2003 SP3)
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
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