0

I have column header which I want to split

Heading XA 2009 WW YY 2010 XXA 2011

I Want output like

XA, WW YY, XXA

Earlier I was using find function in excel which was working fine

=MID("XA 2009",1,FIND(" ","XA 2009",FIND(" ","XA 2009")+1)-1)

OUTPUT AS XA, WW YY

Now requirement has change to code in vba

I was trying to use Instr() instead of find as it is not working in VBA

Mid("XA 2009", 1, InStr(1, "XA 2009", " ", InStr(1, "XA 2009", "2")) - 1)

Now the output is XA, WW instead of WW YY.

Can anyone suggest what I am doing wrong. I am pretty new to vba.

I Want output like

XA, WW YY, XXA

I am using excel 2013

AndroidCrop
  • 109
  • 1
  • 6
Mohit
  • 69
  • 2
  • 15
  • I've just updated my answer with the complete and tested solution for your edited requirement. Please mark it as the solution for your question. – Israel Aug 12 '15 at 12:13

2 Answers2

1

First, see in the answer for the following SO question, the general approach and prerequisites for using Regex search in VBA:

How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

Now, as for your specific requirement, try the following pattern:

(\D*)\s+\d*\s+(\D*)\s+\d*\s+(\D*)\s+\d*

It will work for your precise example, but if you need the input string to be a bit more general you might need to modify the pattern.

Some explanations:
\D* will match one or more non numerical text characters ("alpha character")
\s+ will match at least one space character
\d* will match one or more numerical digits
the (parenthesis) are for grouping sets of results, so I used them to surround what you wish to extract from the input string.

If for example you know for sure that there's only one white-space character you can use:
[\s]
So the pattern might look like:

(\D*)[\s]\d*[\s](\D*)[\s]\d*[\s](\D*)[\s]\d*

Also, this is a great tool for online pattern testing:

https://regex101.com/

This is the solution for your edited requirement:

  1. In the VBA editor, go to tools=>references, find and select the checkbox next to "Microsoft VBScript Regular Expressions 5.5", press ok

  2. add this code to "ThisWorkbook" module:

    Private Sub solution()
    Dim regEx As New RegExp
Dim strPattern As String
Dim myInput As Range
Dim myOutput As Range

Set myInput = ActiveSheet.Range("A1")
Set myOutput = ActiveSheet.Range("A2")

strPattern = "(\D*)[\s]\d*[\s](\D*)[\s]\d*[\s](\D*)[\s]\d*"

strInput = myInput.Value

With regEx
    .Global = True
    .MultiLine = True
    .IgnoreCase = False
    .Pattern = strPattern
    End With

    If regEx.test(strInput) Then
        ActiveSheet.Range("A2") = regEx.Replace(strInput, "$1, $2, $3")
    End If
End Sub
Community
  • 1
  • 1
Israel
  • 1,184
  • 2
  • 13
  • 26
0

You probably had that formula in a specific cell, right? I think the following should do:

Range("yourcell").FormulaR1C1 = "=MID("XA 2009",1,FIND(" ","XA 2009",FIND(" ","XA 2009")+1)-1)"

Just replace "yourcell" with the cell number you had the formula in. So if you had it in cell A1, for example, it should be Range("A1")

Looker
  • 144
  • 1
  • 2
  • 13