How to insert special character before "E" character in a word

[pra_kale]

Hi,

In a column I have a words with a length of 5 with a combination of numbers and characters. I want to insert "_" after character "E" only if presiding character is a number. For e.g. if word is  081e5 then i want 081e_5, 1e124 then 1e_124, 12e45 then 12e_45 else the word should be as it is.

Can you please help to resolve this.

Thanks in advance.

[Gysbert_Wassenaar]

=If(IsNum(Mid('081e5',Index('081e5','e')-1,1)), replace('081e5', 'e', 'e_'),'081e5')

[pra_kale]

Hi Thanks Gysbert for your help.

But, the thing is just for e.g. purpose I have hard-coded the text but in live scenario there is a column called MEMBERNO which consist these scenarios. Then in that case how to handle this.

Thanks once again,

[Gysbert_Wassenaar]

Replace the example string with the field name

[pra_kale]

Hi,

I have replaced the string with field name and now it is working  absolutely fine.

But, if in a word last character is "e" then it should not get replaced ,currently it is getting replaced.

e.g. 1234E get replaced with 1234E_

[Gysbert_Wassenaar · Accepted solution]

Well, that's what you asked for. You didn't specify any conditions for the case where e is the last letter.

You can add another test to see if e is the last letter, ie if the index of e equals the length of the string.

=If(IsNum(Mid(MEMBERNO,Index(MEMBERNO,'e')-1,1))
    AND Index(MEMBERNO,'e',-1) <> Len(MEMBERNO),
    replace(MEMBERNO,'e','e_'),MEMBERNO)

[pra_kale]

Yes I missed out at first place about mentioning the conditions...

Gysbert, the logic given by you is really working fine..You are too good.

Thanks once again Gysbert.