Formula, separate City & State

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to help in separating a city, state column. The column is comma
delimated, there are also blank fields in the column. I basically need to
separate the one column to make two, one for city and one for state. I
appreciate any help you can give me. Thanks
 
Try, assuming that the city is the first

City: Left([FieldName],Instr([FieldName],",") - 1)

State: Mid([FieldName],Instr([FieldName],",") + 1)
 
Thanks that worked great, except for one thing. I do have fields in that row
which are blank. If they are blank I want them to remain blank. After using
that formula it now has a #ERROR everywhere where that was a blank. How can
I change the function in order to account for the blanks and #ERROR??

Ofer said:
Try, assuming that the city is the first

City: Left([FieldName],Instr([FieldName],",") - 1)

State: Mid([FieldName],Instr([FieldName],",") + 1)

--
\\// Live Long and Prosper \\//
BS"D


Chris said:
I need to help in separating a city, state column. The column is comma
delimated, there are also blank fields in the column. I basically need to
separate the one column to make two, one for city and one for state. I
appreciate any help you can give me. Thanks
Click to expand...
Click to expand...
 
Try

City: IIf(Instr([FieldName],","), Left([FieldName],Instr([FieldName],",") -
1), [FieldName])

State: Mid([FieldName],Instr([FieldName],",") + 1)


--
\\// Live Long and Prosper \\//
BS"D


Chris said:
Thanks that worked great, except for one thing. I do have fields in that row
which are blank. If they are blank I want them to remain blank. After using
that formula it now has a #ERROR everywhere where that was a blank. How can
I change the function in order to account for the blanks and #ERROR??

Ofer said:
Try, assuming that the city is the first

City: Left([FieldName],Instr([FieldName],",") - 1)

State: Mid([FieldName],Instr([FieldName],",") + 1)

--
\\// Live Long and Prosper \\//
BS"D


Chris said:
I need to help in separating a city, state column. The column is comma
delimated, there are also blank fields in the column. I basically need to
separate the one column to make two, one for city and one for state. I
appreciate any help you can give me. Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top