String of airport codes (all 3 letters each) need to extract each code on diff cell

**thanhie** · 06-04-2015, 12:37 PM

Hello, how do I extract every three letters representing an airport code from a string and put it on a seperate cell? Ultimately I need to count how many flights were taken and which flights are intl and which are domestic. I need these codes seperated from the string.

An example of a string with airport codes seperated by a period is below. Can you show me the formula?

Myr.clt.ord.nrt

Then there is a string where the codes are seperated by both a space and a period. Can you please show me the formula?

Gig.atl mex.fln. bna gig

The there is a string just seperated by spaces can you please show me the formula?

Clt mex clt

Thanks so much!!

**daffodil11** · 06-04-2015, 12:43 PM

Select Column, Ctrl+H, replace "." with " "

Data->Text To Columns, Delimited, Choose Space, Hit Finish

**mehmetcik** · 06-04-2015, 12:47 PM

Hi

You should first convert your data so it is all in the same format

The substitute function will replace all spaces with periods.

If your data in in A1 then

Formula:

Please Login or Register  to view this content.

If you use that formula to convert all your data and copy paste value to fix your data

Then you can use the Text to Columns function under the data tab to move your data into separate cells

String of airport codes (all 3 letters each) need to extract each code on diff cell

LinkBack

Thread Tools

Rate This Thread

Display

String of airport codes (all 3 letters each) need to extract each code on diff cell

Re: String of airport codes (all 3 letters each) need to extract each code on diff cell

Re: String of airport codes (all 3 letters each) need to extract each code on diff cell

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] I need to extract first 4 letters from a cell

for each cell in range in wb1 manipulate string & paste to wb2 diff col same row

[SOLVED] extract text string when you only know the last three letters of that string

[SOLVED] Extract two zip codes from one cell with lots of content

extract letters from an alphanumeric string excel

Bookmarks

Bookmarks

Posting Permissions