Special Character to replace character

Hello everyone,

I have a sample (Three excel columns) :

M2 B7 41087A2000
M2 B7 41087B4000AD030
M2 B7 41087B2000
M2 B7 41087C3000

I want to know how many instances number 2, 3 and 4 i have. Instance number two (2000), number three (3000) and number four (4000) which are bold in my sample.

How can i replace the letter A,B,C,D,E… before the instance ?

.Contain(“41097” + - + “2000”)

If you know the length of the string up to the letter (in column 3) will always be 5, you can do this:
str = str.Substring(0,4) + "ENTER_REPLACEMENT_HERE" + str.Substring(6,str.Length);

Depending on the size of the Excel sheet, you could read the sheet into a datatable and filter into separate datatables, one for 2000, one for 3000, one for 4000. If you just need the count, you could use a select statement that finds how many lines have for example a letter followed by 2000 (i.e. A2000, B2000, etc), and do the same for 3000/4000.

Hi @mike.vansickle,

Yes, the lenght won’t change.

Thank you for your reply. For my information, could you explain me what does it mean (0,4) and (6,str.Length) ?


If you assigned str = 41087A2000;
str.Substring(0,4) will give you 41087
str.Substring(6, str.Length) will give you 2000

If you assigned str = 41087B4000AD030;
str.Substring(0,4) will give you 41087
str.Substring(6, str.Length) will give you 4000AD030

So since the start will always be the same length, str.Substring(0,4) just gives us the first 5 indices/characters (since they are zero indexed) of the string (i.e. 41087).

The str.Substring(6, str.Length) gives us the text from the 6th index, or the 7th position, all the way to the end of the string. So regardless if there is excess on the end of the string or not it will capture it since the only thing you want to remove/replace is the “A”, “B”, “C”, etc in the string.

1 Like

Thank you @mike.vansickle !

You’re helping me and i think others. I appreciate :slight_smile:

See you !

1 Like