拆分不一致分隔符的字符串 [英] Split string with inconsistent delimiter

问题描述

  CITY_STATE_ZIP 
 ----- --------------------------------------------- 
门罗，IN 46711 
南本德，IN 46615 
亚历山大，IN 46001 
  

我希望将三个单词'CITY_STATE_ZIP'分割成不同的列。

  CITY STATE ZIP 
 ----- ----- ---------- -------------------- 
门罗IN 46711 
南弯IN 46615 
亚历山大IN 46001 
  

解决方案

你可以如下，如果你想使用VBA。

  Sub splitIntoCols（）
 
 Dim oRange As Excel .Range 
 Dim oCell As Excel.Range 
 Dim vValue As Variant 
 Dim sCity As String 
 Dim sState As String 
 Dim sZipCode As String 
 
设置oRange = ActiveWorkbook.Sheets （1）.Range（A3：A100）
 
对于每个oCell在oRange 
 
'获取整个值
 vValue = oCell.Value 
 
'检索城市名称（带或不带空格）
 sCity = Left（vValue，InStr（vValue，，） -  1）
 
'删除城市名称从数组
 vValue = Trim（Mid（vValue，InStr（vValue，，）+ 1））
 
'将空格拆分为空格
 vValue = split（vValue ，）
 
 sState = vValue（0）
 sZipCode = vValue（1）
 
下一个
 
 End Sub 
  

I have a column that contains city, state and zip code.

CITY_STATE_ZIP                                           
--------------------------------------------------
Monroe, IN 46711
South Bend, IN 46615
Alexandria, IN 46001

I wants the three words 'CITY_STATE_ZIP' to be split into different columns.

      CITY STATE      ZIP
---------- ---------- --------------------
    Monroe IN         46711
South Bend IN         46615
Alexandria IN         46001

解决方案

You can do it as follows, if you want to use VBA.

Sub splitIntoCols()

    Dim oRange As Excel.Range
    Dim oCell As Excel.Range
    Dim vValue As Variant
    Dim sCity As String
    Dim sState As String
    Dim sZipCode As String

    Set oRange = ActiveWorkbook.Sheets(1).Range("A3:A100")

    For Each oCell In oRange

        'Takes the whole value
        vValue = oCell.Value

        'Retrieve the City name (with or without spaces)
        sCity = Left(vValue, InStr(vValue, ",") - 1)

        'Remove the city name from the array
        vValue = Trim(Mid(vValue, InStr(vValue, ",") + 1))

        'Split the value by spaces
        vValue = split(vValue, " ")

        sState = vValue(0)
        sZipCode = vValue(1)

    Next

End Sub

这篇关于拆分不一致分隔符的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！