CODEWITHSUNDEEP
regexexcel
Ajay
Ajay

Reputation: 6590

Regular Expression in ms excel

How can I use regular expression in excel ?

enter image description here

In above image I have column A and B. I have some values in column A. Here I need to move data after = in column B. For e.g. here in 1st row I have SELECT=Hello World. Here I want to remove = sign and move Hello world in column B. How can I do such thing?

Upvotes: 0

Views: 159

Answers (3)

Paresh J
Paresh J

Reputation: 2419

You can simply use Text to Column feature of excel for this:

Follow the below steps : 
1) Select Column A.
2) Goto Data Tab in Menu Bar.
3) Click Text to Column icon.
4) Choose Delimited option and do Next and then check the Other options in delimiter and enter '=' in the entry box.
5) Just click finish.

Here are URL for Text to Column : http://www.excel-easy.com/examples/text-to-columns.html

Upvotes: 0

Rick Hitchcock
Rick Hitchcock

Reputation: 35670

Stackoverflow has many posts about adding regular expressions to Excel using VBA. For your particular example, you would need VBA to actually move a substring from one cell to another.

If you simply want to copy the substring, you can do so easily using the MID function:

=IFERROR(MID(A1,FIND("=",A1)+1,999),A1)

enter image description here

I used 999 to ensure that enough characters were grabbed.

IFERROR returns the cell as-is if an equals sign is not found.

To return the portion of string before the equals sign, do this:

=LEFT(A1,FIND("=",A1&"=")-1)

In this case, I appended the equals sign to A1, so FIND won't return an error if not found.

Upvotes: 2

rusk
rusk

Reputation: 300

You can use the Text to Column functionality of MS-Excel giving '=' as delimiter.

Refer to this link: Chop text in column to 60 charactersblocks

Upvotes: 0

Related Questions