[已解决]户籍判断

dyzx

请各位老师帮忙，如果我要对C列户籍进行判断，以“广东省中山市”为基础，如果有“广东省中山市”的户籍就判断为”本市“，如果是“广东省中山市”之外的市为”本省外市“，如果是”广东省“之外的省就称为”外省“，多谢各位指教。

最佳答案

月排行榜 / 总排行榜

七彩屋

2016-10-31 12:17

Sub qcw()
Dim m As Integer
m = Range("C65536").End(xlUp).Row
For x = 3 To m
  If InStr(Cells(x, 3), "中山市") > 0 Then
    Cells(x, 4) = "本市"
  ElseIf Left(Cells(x, 3), 6) <> "广东省中山市" And Left(Cells(x, 3), 3) = "广东省" Then
    Cells(x, 4) = "本省外市"
  Else
    Cells(x, 4) = "外省"
End If
Next
End Sub

跳转到最佳答案楼层

砂海

D3
=IF(COUNTIF(C3,"*广东省中山市*"),"本市",IF(COUNTIF(C3,"*广东省*"),"本省外市","外省"))

七彩屋

Sub qcw()
Dim m As Integer
m = Range("C65536").End(xlUp).Row
For x = 3 To m
  If Left(Cells(x, 3), 6) = "广东省中山市" Then
    Cells(x, 4) = "本市"
  ElseIf Left(Cells(x, 3), 6) <> "广东省中山市" And Left(Cells(x, 3), 3) = "广东省" Then
    Cells(x, 4) = "本省外市"
  Else
    Cells(x, 4) = "外省"
End If
Next
End Sub

dyzx

七彩屋 发表于 2016-10-31 10:34
Sub qcw()
Dim m As Integer
m = Range("C65536").End(xlUp).Row

老师：如果C列只有“中山市**镇”，能否也能判断为“本市”，多谢指教。

dyzx

砂海 发表于 2016-10-31 10:32
D3
=IF(COUNTIF(C3,"*广东省中山市*"),"本市",IF(COUNTIF(C3,"*广东省*"),"本省外市","外省"))

两位老师：如果C列只有“中山市**镇”，能否也能判断为“本市”，多谢指教。

dyzx

砂海 发表于 2016-10-31 10:32
D3
=IF(COUNTIF(C3,"*广东省中山市*"),"本市",IF(COUNTIF(C3,"*广东省*"),"本省外市","外省"))

老师：如果C列只有“中山市**镇”，能否也能判断为“本市”，多谢指教

砂海

=IF(COUNTIF(C3,"*中山市*镇*"),"本市",IF(COUNTIF(C3,"*广东省*"),"本省外市","外省"))
但有风险
譬如"中山市场镇南街道"

七彩屋

Sub qcw()
Dim m As Integer
m = Range("C65536").End(xlUp).Row
For x = 3 To m
  If InStr(Cells(x, 3), "中山市") > 0 Then
    Cells(x, 4) = "本市"
  ElseIf Left(Cells(x, 3), 6) <> "广东省中山市" And Left(Cells(x, 3), 3) = "广东省" Then
    Cells(x, 4) = "本省外市"
  Else
    Cells(x, 4) = "外省"
End If
Next
End Sub

dyzx

七彩屋 发表于 2016-10-31 12:17
Sub qcw()
Dim m As Integer
m = Range("C65536").End(xlUp).Row

七彩屋老师：多谢指教，多谢