Using Regex.Replace function in c# program to replace a string(data) with "data"?

Question

I want to make use of Regex.Replace function to replace data in the format

05-11

to

"05-11"

so that excel can read it as a string.

Excel is converting the data to 05-Nov even though that particular column is defined as char.

In my application code, I have the below piece of code to replace any data that starts with a dash (-) with double quotes, "data"

var newString = Regex.Replace(data, @"^(-.*)$", "=\"$0\"");

How can I make use of this function to replace any data which are like

'05-11', '15-2019'

with

"05-11", "15-2019"

for the excel to read them as a string not as date format.

Instead of regex maybe you can try https://superuser.com/questions/908548/excel-how-to-enter-text-verbatim/908550. — Yarl, Jan 29 '19 at 15:13
Make that '05-11 instead of 05-11. This indicates to Excel to treat the value as string literal. — Fildor, Jan 29 '19 at 15:14
Hi Fildor, Can i do this in the code? instead of changing the excel format? — ai03, Jan 29 '19 at 16:25
Just to point out that you don't *need* to do this in code. After loading in excel, just select the relevant cells and change the format. — Richardissimo, Jan 29 '19 at 23:20
Possible duplicate of [Stop Excel from automatically converting certain text values to dates](https://stackoverflow.com/questions/165042/stop-excel-from-automatically-converting-certain-text-values-to-dates) — Richardissimo, Jan 29 '19 at 23:22
@Richardissimo That doesn't work for automatic datetime conversion - once Excel has converted to a timestamp, there isn't an easy path back to the original string. — NetMage, Jan 30 '19 at 20:27

NetMage · Accepted Answer · 2019-01-29T23:17:02.797

Unfortunately Excel does not accept " as an indicator of a text column, the only way to be sure is to proceed the value with a single quote.

var newstring = Regex.Replace(data, @"\b""?(\d\d-(?:\d\d)?\d\d)""?\b", "\"=\"\"$0\"\"\"");

This finds possibly double-quoted date strings that constitute the whole column value, and outputs it quoted with an equals sign and double quotes.

Unfortunately this special formatting is lost if you save as CSV from inside Excel and try to reload.

See this question for details.

1 Answers1