Can Someone Help with Formula

MrData

Active Member
Joined
May 4, 2003
Messages
323
I have approximatly 35,000 records in a similar format to the 4 records shown below. What I need to do is put the destination code into the next cell. The destination is always between the word to and the country in brackets.

1.13 B to DA [Germany] The result should read DA
213.27 BC to S AW2 [USA] The result should read S AW2
2.16 DVB to BA SJ [Finland] The result should read BA SJ
1.1543 B to DA 1DCDS [Germany] The result should read DA 1DCDS

Can anyone help with the formula as I cannot get it to work?

Thanks
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
Try this:

=MID(A5,SEARCH(" to ",A5,1)+4,500) where A5 is the text field.

Ensure that you include the spaces between the quotes, or you might pick up other words (eg Toronto)
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,022
=MID(A1,FIND("to",A1,1)+3, FIND("[",A1,1)-FIND("to",A1,1)-4)

seems to work on the test values you gave.
 

MrData

Active Member
Joined
May 4, 2003
Messages
323
tactps & Greg,

Thanks to both for your help. I've got what I needed by using Greg's formula.

Cheers

Mike
 

MrData

Active Member
Joined
May 4, 2003
Messages
323

ADVERTISEMENT

Greg,

I've been trying to work out the formula you supplied. Is there any chance you can explain what's happening with the +3 and -4?

Cheers

Mike
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
=TRIM(MID(A1,SEARCH("to",A1)+2,SEARCH("[",A1)-SEARCH("to",A1)-2))

might be easier to apply.

2 is the lenght of the word "to".
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,022
Mike,

The +3 and -4 are to adjust the position returned by the FIND(). FIND returns the position of the first character, so we need to offset by the length of the string we sought. I just quickly eyeballed Aladin's but it looks to offer a couple of improvements to mine: 1. It uses SEARCH() instead of FIND(). SEARCH is not case-sensitive, whereas FIND is and 2. He's not "hard-coding" the offsets to include 1 space like I did, rather he is using TRIM to do away with extraneous spacing. More robust - someone can accidentally enter two spaces without causing the formula to fail. One side note - use of TRIM also reduces any double spaces inside the string being trimmed to one space. Normally, this is desirable, but just to make you aware in case for some reason you wanted two spaces in the middle somewhere and couldn't figure out where they'd gone.

A good way to follow formulas' work is (XL2002, not sure about prev versions) to use Tools | Formula Auditing... | Evaluate Formula.

Regards,
 

MrData

Active Member
Joined
May 4, 2003
Messages
323
Greg,

Thanks for the explanation, its helped me understand what's happening. You are right about using the TRIM function, I was using your formula then copying and pasting values, before using replace for double spaces.

Cheers

Mike
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,322
Messages
5,769,448
Members
425,547
Latest member
Iano

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top