# Special Character to replace character

Hello everyone,

I have a sample (Three excel columns) :

M2 B7 41087A2000
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.

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) ?

Thanks

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

See you !

1 Like