Excel macros and regular expression

Question

I have a column in excel sheet which contains some data. I want to write a macros to replace the cell content with the words which appears before a word with special character. For example:

Cell content:

M.Ramalingam                  S/o Mahalingam

Should be replaced by:

M.Ramalingam

Cell content:

Balkis Beevi                      W-o Mahamed Ali Jinna

Shoud be replaced by:

Balkis Beevi

I see in one case, your special character is ., in the other it's the second space? How do you determine which string has which special character to look for? — BruceWayne
– BruceWayne, Commented Aug 11, 2015 at 17:12
In first case special character is '/' and in second case it is '-'. I am not considering '.' as a special character. All the word before a word with special character is to be captured — Samir
– Samir, Commented Aug 12, 2015 at 2:11

brettdj · Accepted Answer · 2015-08-12 03:13:08Z

1

Something like

Function StrNew(strIn As String) As String
Dim objRegexp As Object
Set objRegexp = CreateObject("vbscript.regexp")
With objRegexp
    .Pattern = "^(.+)\b.+[\-\/].*?$"
    StrNew = .Replace(strIn, "$1")
End With
End Function

answered Aug 12, 2015 at 3:13

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

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

Comments

leal32b · Accepted Answer · 2015-08-11 17:22:00Z

0

You should use a custom formula:

Function CHANGE(text As String, char As String)
    Dim sArray() As String

    sArray() = Split(text, char)

    CHANGE = Trim(sArray(0))
End Function

Then you can use it this way in the cells: "=CHANGE(B2;"S/o")" The first parameter is the cell with the text you want to split and the second one is the special character.

I hope this helps you...

answered Aug 11, 2015 at 17:22

leal32b

3941 gold badge3 silver badges11 bronze badges

Collectives™ on Stack Overflow

Excel macros and regular expression

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related