Large field into many

[nati]

hi,

I have been given an old fm db with two fields in it, one with a first and last name and one with an address, city, state and zip. I need to get the city state and zip out of the second field into new fields for each. Can I import them into separate fields or is there a better way... Thoughts?

many thanks,

nati

[ June 02, 2001: Message edited by: nati ]

[JPaul]

Hello Nati,

to split a large field and export it as many fields you have to accomplish various steps:

• create a global field contaning a TAB character (chr(9))

• create another global in which copy the splitted informations contained in your large field, separating each of them by your global containing TAB

• export (as a tab separated) your fields, exluding the TAB global and your large field

• import this file in an appropriate db containing all the fields new created.

Regards

[ June 02, 2001: Message edited by: JPaul ]

[Chuck]

Assuming that the field you have is called City_State_Zip and you want to get the information into three fields, City, State and Zip, and the information in City_State_Zip is formated like this:

City, ST 00000

with a single space between the comma and the state and a single space between the state and the zip, go to a layout with the City, State and Zip fields on it. Click into the City field and choose Replace from the Records menu. Select calculated replace and enter the following calculation:

Left( City_State_Zip, Position( City_State_Zip, ",", 1, 1 ) - 1 )

This says to take the text of City_State_Zip and extract all of the characters before the comma.

For State, use this calculation:

Middle( City_State_Zip, Position ( City_State_Zip, ", ", 1, 1 ) + 1, 2 )

For Zip, use this calculation:

RightWords( City_State_Zip, 1 )

If your zip codes could have the plus four portion, then you need to use this calculation:

Case(

[nati]

Thanks! I'm working it out now...

Nati