从字符串中提取文本 - Excel VBA

I am trying to generate X,Y,Z strings from a text file to input into a CAD program like AutoCAD or Inventor. I would like to do this in Excel using VBA.

我试图从文本文件生成X,Y,Z字符串,以输入到AutoCAD或Inventor等CAD程序中。我想在Excel中使用VBA执行此操作。

The text file contains strings like this:

文本文件包含如下字符串:

G0X.5384Z.05
G1X.634Z-.0327F.004
Z-.9184F.006
X.592Z-.9548F.004

and I would like to extract X, Y, and Z coordinates from that. To be clear, this text has been pasted into Excel and Column A contains each line. Line 1 would come out as "X.5384" in one column and "Z.05" in another.

我想从中提取X,Y和Z坐标。为了清楚起见,此文本已粘贴到Excel中,而A列包含每一行。第1行将在一列中显示为“X.5384”,在另一列中显示为“Z.05”。

I know enough VBA to remove the XYZ from the strings but I cannot figure out how to pull a specific portion out. There is no guarantee that they will be in XYZ order or another letter will not be in the middle of them.

我知道足够的VBA从字符串中删除XYZ,但我无法弄清楚如何拉出特定部分。无法保证它们将处于XYZ顺序或其他字母不在其中间。

I have read a bit about the Regex.Split and with enough time I could probably get it to split the entire string out but I would like to just pull X, Y, and Z coordinates out and ignore the rest if possible.

我已经阅读了一些关于Regex.Split的内容,并且有足够的时间我可能会把它分开整个字符串,但我想把X,Y和Z坐标拉出来,如果可能的话忽略其余的。

2 个解决方案

#1

First put this little User Defined Function in a standard module:

首先将这个小用户定义函数放在标准模块中:

Public Function GetData(r As Range, CH As String) As String
   Dim v As String, i As Long, j As Long, L As Long, CHm As String

   v = r.Text
   L = Len(v)
   GetData = ""
   i = InStr(1, v, CH)
   If i = 0 Then Exit Function
   GetData = CH

   For j = i + 1 To L
      CHm = Mid(v, j, 1)
      If CHm = "-" Or CHm = "." Or CHm Like "[0-9]" Then
         GetData = GetData & CHm
      Else
         Exit Function
      End If
   Next j
End Function

Then in B1 enter: =getdata(A1,"X") and copy down.
In C1 enter: =getdata(A1,"Y") and copy down.
In D1 enter: =getdata(A1,"Z") and copy down.

然后在B1输入:= getdata(A1,“X”)并复制下来。在C1中输入:= getdata(A1,“Y”)并复制下来。在D1中输入:= getdata(A1,“Z”)并复制下来。

#2

For an alternate solution that does not use VBA, use row 1 as a header row and put in the letters you're looking for, as shown in this image:

对于不使用VBA的备用解决方案,请使用第1行作为标题行并输入您要查找的字母,如下图所示:

In cell B2 is this formula and then copy over and down:

在单元格B2中是这个公式,然后上下复制:

=IF(COUNTIF($A2,"*"&B$1&"*")=0,"",MID($A2,SEARCH(B$1,$A2),MATCH(FALSE,INDEX(ISNUMBER(--(MID($A2&" ",SEARCH(B$1,$A2)+1,ROW($1:$10))&0)),),0)))

#1