Finding the Alphabet in a String

madhuchelliah

Board Regular
Joined
Nov 22, 2017
Messages
211
Office Version
  1. 2013
Platform
  1. Windows
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
CD
4A2154-255 254B, 5545E, D454-D444 258BA454
52154-255 254AB, 5545AE, AD454-AD444 258BA455
Sheet2
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,605
Office Version
  1. 365
Platform
  1. Windows
Here is another user-defined function you could consider.

VBA Code:
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
CDE
4A2154-255 254B, 5545E, D454-D444 258BA4542154-255 254AB, 5545AE, AD454-AD444 258BA454
Sheet1
Cell Formulas
RangeFormula
E4E4=ReplaceChar(D4,C4)
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

madhuchelliah

Board Regular
Joined
Nov 22, 2017
Messages
211
Office Version
  1. 2013
Platform
  1. Windows
Here is another user-defined function you could consider.

VBA Code:
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
CDE
4A2154-255 254B, 5545E, D454-D444 258BA4542154-255 254AB, 5545AE, AD454-AD444 258BA454
Sheet1
Cell Formulas
RangeFormula
E4E4=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.
 

Forum statistics

Threads
1,141,302
Messages
5,705,584
Members
421,400
Latest member
chakam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top