Finding the Alphabet in a String

[madhuchelliah]

Hello all, i have a string in D4. Length of the string is dynamic. Within the string, alpha character will be always single character (A to Z) or double character(AA to ZZ). I want to find the single character alpha within the string and insert the alpha character given in the cell C4. Leave the double alpha character as it is. Please see the example below for clarity. Thank you.

Book1
	C	D
4	A	2154-255 254B, 5545E, D454-D444 258BA454
5		2154-255 254AB, 5545AE, AD454-AD444 258BA455
Sheet2

 

[Peter_SSs]

Here is another user-defined function you could consider.

Function ReplaceChar(sOrig As String, sRep As String) As String
  Dim RX As Object
 
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "(^|[^A-Z])([A-Z])(?=[^A-Z]|$)"
  ReplaceChar = RX.Replace(sOrig, "$1" & sRep & "$2")
End Function

madhuchelliah.xlsm
	C	D	E
4	A	2154-255 254B, 5545E, D454-D444 258BA454	2154-255 254AB, 5545AE, AD454-AD444 258BA454
Sheet1

Cell Formulas
Range	Formula
E4	E4	=ReplaceChar(D4,C4)

 

[madhuchelliah]

You've nice solution from @FormR in post#7

Thank you Sandy for your time.

 

[madhuchelliah]

Here is another user-defined function you could consider.

Function ReplaceChar(sOrig As String, sRep As String) As String
  Dim RX As Object

  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "(^|[^A-Z])([A-Z])(?=[^A-Z]|$)"
  ReplaceChar = RX.Replace(sOrig, "$1" & sRep & "$2")
End Function

madhuchelliah.xlsm
	C	D	E
4	A	2154-255 254B, 5545E, D454-D444 258BA454	2154-255 254AB, 5545AE, AD454-AD444 258BA454
Sheet1

Cell Formulas
Range	Formula
E4	E4	=ReplaceChar(D4,C4)

Hello Pete, yours also working great. Thanks for chiming in. But unfortunately i can mark one solution alone. Thanks for your time.

 

[Peter_SSs]

Hello Pete, yours also working great. Thanks for chiming in.

You're welcome.

But unfortunately i can mark one solution alone.

That isn't a problem to me - you just mark the one that suits you the best.