Splitting one field into two based on contents. (1 Viewer)

[Anchoress]

I am running Office2000.

I am copying data from the YellowPages site and pasting it into a memo field. The data shows up like this:

0.4 km General Nutrition Centre - 905-731-8623
1 promenade Circle , Thornhill , ON L4J 4P8
Category : Health Foods-Retail
Map & Directions www.9057318623.yp.ca Save Share Add to Facebook

​

I'd like to cut the proximity data (number in kilometres) from the beginning and put it in its own field; is this possible?

99% of the time the format will be the same: "#.# km ", but there's a small chance there might be some listings with two digits before the period.

Thanks for any help.

 

[pbaldy]

You can use the Left & Mid functions along with the InStr function to find the "km" (Left for the first field, Mid for the second).

 

[Anchoress]

Thanks for the quick reply

You can use the Left & Mid functions along with the InStr function to find the "km" (Left for the first field, Mid for the second).

Thank you! This won't be screwed up if there happens to be another 'km' elsewhere in the field, will it?

 

[pbaldy]

The InStr function should return the first occurrence found, which looks like it will work for your situation.

 

[DCrake]

Simple Software Solutions

Be careful you don't get done for plagurism. Purloining data from other people who may have paid for the information in the first place will not go down nicely if they find out.

Be ready to come up with a solution on how you obtained the data should the phone ring or you get a knock on the door.

CodeMaster: