vba使用instr和left / right从字段中拉取名称 [英] vba pulling names from fields using instr and left/right

问题描述

每个人再见我我试图拉各种字符串，并在一个单独的字段中放置一个字符串。我似乎无法让我的代码工作，我希望有人可以在我有缺陷的尝试中发光。非常感谢。

我的列B包含多个名称，格式如下：

  BCD 
其他团队teamOne teamTwo 
 / jdoe / smithjr / 
 / someguy / testuser / 
 / obiwan / luke / darth 
 / vader / 
 / hp / dell / lenova / mint / 
  

我只需要取其前两个名称，并在一个字段中放置一个名称，在另一个字段中放置一个名称。

预期结果

  BCD 
其他团队teamOne teamTwo 
 / jdoe / smithjr / jdoe smithjr 
 / someguy / testuser / someguy testuser 
 / obiwan / luke / darth obiwan luke 
 / vader / vader 
 / hp / dell / lenova / mint / hp dell 
  

到目前为止我已经提出的代码是下面的并且不起作用。我收到错误，没有数据可以替换。

 对于Q = 2到10 
如果UCase（Cells（ Q，B）值）像*其他团队*然后
名称=单元格（Q，B）值
 startingPosition = InStr（Name，/）
 Firstname = Left（Name，（startingPosition） -  1）
 secondName = Right（Name，startingPosition  -  2）
单元格（Q，C）Value = Firstname 
单元格，D）Value = secondName 
 End If 
 Next Q 
  

我对VBA很新，（第3天），似乎无法弄清楚如何解析这些数据。也许有人可以解释我做错了什么，帮助我？谢谢。

解决方案

由于你有一个分隔符的字符串，我个人使用 Split 函数。注意，你的如果UCase（Cells（Q，B）。Value）像* Other Team *那么测试在你的样本数据的每个输入上都失败了仅适用于您跳过的列标题，假设将测试更改为大写，就像输入一样。如果你想确定你是否在正确的工作表上，那就需要在循环之外。

这样的一个例子：

 如果UCase $（Cells（1，B）。Value）Like* OTHER TEAM *然后
 Dim tokens（）As字符串
对于Q = 2到10 
名称=单元格（Q，B）。值
 tokens = Split（Name，/）
如果UBound（令牌） > 0 Then Cells（Q，C）。Value = tokens（1）
如果UBound（令牌）> 1 Then Cells（Q，D）。Value = tokens（2）
 Next Q 
 End If 
  

Me again everyone. I am trying to pull various strings and place a string in a separate field. I cannot seem to get my code to work and I am hoping someone can shine some light in my flawed attempts. Thank you very much.

I have column "B" which includes multiple names and is in the following format:

       B                    C         D
   Other Team            teamOne     teamTwo
 /jdoe/smithjr/
 /someguy/testuser/
 /obiwan/luke/darth
 /vader/
 /hp/dell/lenova/mint/

I only need to take the first two names and place one name in one field and one name in another field.

Expected results

       B                    C         D
   Other Team            teamOne     teamTwo
 /jdoe/smithjr/            jdoe       smithjr
 /someguy/testuser/       someguy     testuser
 /obiwan/luke/darth        obiwan     luke
 /vader/                   vader
 /hp/dell/lenova/mint/     hp         dell

The code I have come up with so far is below and does not work. I receive errors that there is no data to replace.

For Q = 2 To 10
If UCase(Cells(Q, "B").Value) Like "*Other Team*" Then
  Name = Cells(Q, "B").Value
  startingPosition = InStr(Name, "/")
  Firstname = Left(Name, (startingPosition) - 1)
  secondName = Right(Name, startingPosition - 2)
  Cells(Q, "C").Value = Firstname
  Cells(Q, "D").Value = secondName
End If
Next Q

I am pretty new to VBA (on 3rd day) and cannot seem to figure out how to parse this data. Perhaps someone can explain what I am doing wrong and assist me? Thank you.

解决方案

Since you have a delimited string, I'd personally use the Split function. Note also that your If UCase(Cells(Q, "B").Value) Like "*Other Team*" Then test fails on every input in your sample data - it is only true for the column header that you skip, assuming that you change the test to uppercase like you do the input. If you're trying to determine if you're on the correct worksheet, it needs to go outside of the loop.

Something like this:

If UCase$(Cells(1, "B").Value) Like "*OTHER TEAM*" Then
    Dim tokens() As String
    For Q = 2 To 10
        Name = Cells(Q, "B").Value
        tokens = Split(Name, "/")
        If UBound(tokens) > 0 Then Cells(Q, "C").Value = tokens(1)
        If UBound(tokens) > 1 Then Cells(Q, "D").Value = tokens(2)
    Next Q
End If

这篇关于vba使用instr和left / right从字段中拉取名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！