Can Someone Help with Formula

MrData

Active Member
Joined
May 4, 2003
Messages
324
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

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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)
 
Upvote 0
=MID(A1,FIND("to",A1,1)+3, FIND("[",A1,1)-FIND("to",A1,1)-4)

seems to work on the test values you gave.
 
Upvote 0
tactps & Greg,

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

Cheers

Mike
 
Upvote 0
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
 
Upvote 0
=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".
 
Upvote 0
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,
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

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
Back
Top