# Can Someone Help with Formula

#### MrData

##### Active Member
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

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
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
=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
tactps & Greg,

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

Cheers

Mike

#### MrData

##### Active Member

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

##### MrExcel MVP
=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
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
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

Replies
2
Views
102
Replies
0
Views
206
Replies
13
Views
392
Replies
6
Views
629
Replies
3
Views
106

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.

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.

### Which adblocker are you using?

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

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