CODEWITHSUNDEEP
regexvbaexcel
Evil Washing Machine
Evil Washing Machine

Reputation: 1341

Strategy to turn a prefix into a suffix for an entire column?

I have a column with data laid out like this:

MO10TRSAB
MO123FJKDAS
MO02JTXZ*

It is basically in the format of MO [0-9]{2,3} [A-Z]+. In plain english, MO followed by 1 to 3 (edited due to my own fail) integers followed by a string of letters and/or symbols of undetermined length (but should be no more than 10; however this is not a requirement)

I want to reverse this for the entire column, make it so that it is of the format [A-Z]+ MO[0-9]{2-3} instead, i.e.:

TRSABMO10
FJKDASMO123
JTXZ*MO02

I am unsure how to do this in excel-VBA mainly because I don't really know how RegEx works in Excel-VBA. Can you guys help?

Upvotes: 0

Views: 112

Answers (4)

PASUMPON V N
PASUMPON V N

Reputation: 1186

Public Function str(ByVal my_string As String) As String()



Dim Numberstring As String
Dim Stringvalue As String

Dim Reversestring() As String




ReDim Reversestring(Len(my_string) - 1)


For i = 1 To Len(my_string)
    Reversestring(i - 1) = Mid$(my_string, i, 1)
Next


For i = 2 To Len(my_string) - 1


     If IsNumeric(Reversestring(i)) Then
          numbersstring = numbersstring & Reversestring(i)

    Else
              Stringvalue = Stringvalue & Reversestring(i)

    End If
Next

finalstring = Stringvalue & "MO" & numbersstring

str = finalstring

End Function

Upvotes: 0

Pradeep Kumar
Pradeep Kumar

Reputation: 6979

Edit: Code updated as per recent edit in question.

Try this:

Insert this code in a Module.

Function ReverseString(ByVal original As String) As String
    Dim re As Object
    Set re = CreateObject("vbscript.regexp")
    re.Pattern = "(MO[\d]{1,3})(.+)"
    re.Global = True
    ReverseString = re.Replace(original, "$2$1")
End Function

Then you can use it whatever way you want to. e.g. You can use it as a formula like this:

=ReverseString(A1)

enter image description here

Upvotes: 0

pnuts
pnuts

Reputation: 59485

With revised requirement and parsing (A1) fixed width at positions 3, 4 and 5 this might suit: 

 =IF(ISTEXT(B1),B1&C1&D1&A1,IF(ISTEXT(C1),C1&D1&A1&B1,D1&A1&B1&C1))

Upvotes: 1

CRondao
CRondao

Reputation: 1903

Or just use this formula:

=IF(ISERROR(VALUE(MID(H10;5;1)));RIGHT(H10;LEN(H10)-4) & LEFT(H10;4);RIGHT(H10;LEN(H10)-5) & LEFT(H10;5))

In this case value is in H10,

Upvotes: 2

Related Questions