Replace Semi Colon by Regular Expression on basis of some criteria

Hi All,

I have a spreadsheet where in few cells, data start with ; (semi colon) and the very next character in not " (double quote). I have to replace only this kind of semi colon by ";" ( Double quote semi-colon double quote).
So the criteria are::::
1. Data in cell start with semi-colon
2. Next character is not equal to double quote
3. Should be replace with ";"

Currently, I am planning to solve this issue be looping each cells --> Finding first chracter ; (semi colon) by using LEFT function --> Finding 2nd Character by MID function and --> then replace that semi-colon.
But, it will be time consuming if there huge data present in the sheet (there will be almost 40000 rows containg data with multiple columns).

Please help me if there is any easiest method or way through which I can replace whole area (like Find-Replace) or is this can be possioble by Regular Expression.


Thanks in advance

Regards
taps

Does it need to be vba?

=IF((LEFT(A2,1)=";")*(MID(A2,2,1)<>""""),REPLACE(A2,1,1,""";"""),A2)

Hi Jindon,

You have given me a wonderful formula, but I think this will work in a particular column only. But my data range from A1 to Z65536 (maximum).
Will this work in that situation?

regards
taps

Hi All,

I have created a function with the above logic as below

Please Login or Register  to view this content.

It is working fine in the small range (large range not tested yet)
Althogh if there is any other solution please let me know.

Regards
taps

Please Login or Register  to view this content.

Hi Jindon,

Thanks a ton
Your function is much much smarter and it is also working nicely.

Cheers

regards
taps