用户定义功能的略微调整 [英] Slight adaptation of a User Defined Function

问题描述

我想从位于excel列内的较大字符串中提取文本和数字的组合.

I would like to extract a combination of text and numbers from a larger string located within a column within excel.

我要使用的常量是每个文本字符串都会

The constants I have to work with is that each Text string will

•以A，C或S开头，并且•将始终为7个字符长•我要提取的弦的位置不同

•either start with a A, C or S, and •will always be 7 Characters long •the position of he string I would like to extract varies

我一直在使用的有效运行的代码是；

The code I have been using which has been working efficiently is;

Public Function Xtractor(r As Range) As String
Dim a, ary
ary = Split(r.Text, " ")
    For Each a In ary
        If Len(a) = 7 And a Like "[SAC]*" Then
            Xtractor = a
            Exit Function
        End If
    Next a
Xtractor = ""
End Function

然而，今天我了解到有时我的数据可能包括这样的情况；

However today I have learnt that sometimes my data may include scenarios like this;

我想要修改我的代码，因此如果第8个字符是下划线"，而第7个字符中的第一个字符是S，A或C，请提取直到下划线"

What I would like is to adapt my code so If the 8th character is "Underscore" and the 1st character of the 7 characters is either S, A or C please extract up until the "Underscore"

其次，我想排除诸如支持"&从中收集".

Secondly I would like to exclude commons words like "Support" & "Collect" from being extracted.

最后，第7个字母应该是数字

Finally the 7th letter should be a number

任何有关此问题的想法将不胜感激.

Any ideas around this would be much appreciated.

谢谢

推荐答案

尝试一下

ary = Split(Replace(r.Text, "_", " "))

或

ary = Split(Replace(r.Text, "_", " ")," ")

两个变体的结果将相同

测试

更新

您知道第七位字符返回字母时如何将结果留空吗?

Do you know how I could leave the result blank if the 7th character returned a letter?

Public Function Xtractor(r As Range) As String
Dim a, ary
ary = Split(Replace(r.Text, "_", " "))
    For Each a In ary
        If Len(a) = 7 And a Like "[SAC]*" And IsNumeric(Mid(a, 7, 1)) Then
            Xtractor = a
            Exit Function
        End If
    Next a
Xtractor = ""
End Function

测试

这篇关于用户定义功能的略微调整的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！