reformating data durring import from excel

[Davschm]

I am importing a table from a website to an excel spreadsheet, cleaning the data a bit, and then importing to Access 2003.

Example record = Name / Age / Location / Contact / Pet / ....... (and so on)

On of the fields I am working with is a Location field with city and state. The format is the same over and over = "City", "State" ( City, ST ). I need to somehow break this data into 2 fields, one for city and one for state. If I can do it while importing that would be fine, or if its easyer to do after its in access thats ok, i just have no idea how.

I am dealing with a few thousand records, so please help me automate this.

Thanks

 

[Bogzla]

I would split them into seperate feilds in excel first, before importing, it should be reasonably straightforward to write a function in excel, something like:

Mid([City_State], 1, (InStr([City_State], ",")-1))
should return anything before the comma

Mid([City_State], (InStr([City_State], ",")+1), ((Len([City_State])) - (Instr([City_Sate], ","))))
should return anything after.

This exact code is not tested but I have used a very similar function before in excel. Hope that this helps some, I'll try write an actual function later if I have time (what is the exact format of the City / state field?). Some of the parentheses probably not necessary but they help me think..

 

[Bogzla]

This seems to do the job, copy into a Module in your excel spreadsheet (or if desired could be used as easily in Access once you have imported the data)

Public Function SplitCityState(City_State As String, CityOrState As String) As String
    If CityOrState = "City" Then
        SplitCityState = Mid(City_State, 1, (InStr(City_State, ",") - 1))
    ElseIf CityOrState = "State" Then
        SplitCityState = Mid(City_State, (InStr(City_State, ",") + 1), ((Len(City_State)) - (InStr(City_State, ","))))
    Else
        SplitCityState = "Invalid 2nd Argument"
    End If
End Function

to call it (in excel) type

=SplitCityState([Cell containing original value], "City")

to get City or

=SplitCityState([Cell containing original value], "State")

to get State. This will literally split whatever is before and after the comma, so it assumes there is no space after the comma. If there is you'll need a slight modification:

Public Function SplitCityState(City_State As String, CityOrState As String) As String
    If CityOrState = "City" Then
        SplitCityState = Mid(City_State, 1, (InStr(City_State, ",") - 1))
    ElseIf CityOrState = "State" Then
        SplitCityState = Mid(City_State, (InStr(City_State, ",") + 2), ((Len(City_State)) - (InStr(City_State, ",") + 1)))
    Else
        SplitCityState = "Invalid 2nd Argument"
    End If
End Function

Hope this is some help (and not too confusing)
Bogzla

 

[Davschm]

Wow, thank you very much for the help. I will give that a try.

I have moved further into the project and am finding new things that i dont know how to do, so i am sure i will be back to this forum with more questions. I am glad everyone here is so helpfull.

Thanks again!