Need excel formula to insert underscore before the Proper case letter where ever find it in a string.

[ManiThani]

I need to replace a string like below:

IpArId to be converted as Ip_Ar_Id

Wherever the upper cases identified in a string to be prefixed with "underscore" ("_").

 

[ManiThani]

any quick reply will help me. Appreciate all your time and effort. Please..

 

[Peter_SSs]

IpArId to be converted as Ip_Ar_Id

Wherever the upper cases identified in a string to be prefixed with "underscore" ("_").

Except if it is the first character in the string?

You could try this user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function Underscore(s As String) As String
  Static RX As Object
  
  If RX Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
  End If
  RX.Pattern = "([A-Z])"
  Underscore = s
  If RX.Test(s) Then Underscore = Left(s, 1) & RX.Replace(Mid(s, 2), "_$1")
End Function

Excel Workbook
	A	B
1	IpArId	Ip_Ar_Id
2
3	cat	cat
4	AbcdEFGH	Abcd_E_F_G_H
5	dOg	d_Og
Sheet2

 

[Peter_SSs]

An alternative would be ..

Function Under_Score(s As String) As String
  Dim i As Long
  
  For i = Len(s) To 2 Step -1
    If Mid(s, i, 1) Like "[A-Z]" Then s = Left(s, i - 1) & "_" & Mid(s, i)
  Next i
  Under_Score = s
End Function