Extracting text from string - Excel VBA

Question

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.

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.

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.

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.

Can you not work off the text file directly?

Mathieu Guindon
– Mathieu Guindon

2016-01-29 19:14:13 +00:00
Commented Jan 29, 2016 at 19:14 — Mathieu Guindon
– Mathieu Guindon, Commented Jan 29, 2016 at 19:14
I could but I have no idea how to do that.

tjb1
– tjb1

2016-01-29 19:19:04 +00:00
Commented Jan 29, 2016 at 19:19 — tjb1
– tjb1, Commented Jan 29, 2016 at 19:19
You could start with this ;-)

Mathieu Guindon
– Mathieu Guindon

2016-01-29 19:27:02 +00:00
Commented Jan 29, 2016 at 19:27 — Mathieu Guindon
– Mathieu Guindon, Commented Jan 29, 2016 at 19:27

Gary's Student · Accepted Answer · 2016-01-29 19:41:11Z

2

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.

answered Jan 29, 2016 at 19:41

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mrbungle Over a year ago

Thats an awesome solution.

tjb1 Over a year ago

Sorry for the late reply but this works great for me. I was able to (probably the wrong way) add a replace code right after GetData = GetData & CHm and pull out X, Y, and Z letters to just leave the coordinates. Thanks for your help.

tigeravatar · Accepted Answer · 2016-01-29 20:56:38Z

1

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:

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

=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)))

answered Jan 29, 2016 at 20:56

tigeravatar

26.7k5 gold badges32 silver badges42 bronze badges

1 Comment

tjb1 Over a year ago

Your code worked great as well but I went with the other as it was easier for me to pull out the X, Y, and Z in VBA. Thanks for your code, I'm still trying to understand it!

Collectives™ on Stack Overflow

Extracting text from string - Excel VBA

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related