Finding the Alphabet in a String

madhuchelliah

Board Regular
Joined
Nov 22, 2017
Messages
226
Office Version
  1. 2019
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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
what about
alpharaw
E2154-255 254B, 5545E, D454-D444 258BA454
A2154-255 254B, 5545E, D454-D444 258BA454
E2154-255 254AB, 5545AE, AD454-AD444 258BA455
 
Upvote 0
what about
alpharaw
E2154-255 254B, 5545E, D454-D444 258BA454
A2154-255 254B, 5545E, D454-D444 258BA454
E2154-255 254AB, 5545AE, AD454-AD444 258BA455
Hello Sandy, i could not grasp your reply. I assuming your concern and replying. Data will be always in D4 cell alone and the character to be inserted always in C4. Both conditions wont change. What i shown in the D5 is expected result in D4 cell. HTH. Let me know if further clarification required. Thank you. Stay safe.
 
Upvote 0
but there are two single characters A and E in the first string and one single character E in the second string so your conditions are not clear or your example is not proper
what you mean by "alpha"
 
Upvote 0
but there are two single characters A and E in the first string and one single character E in the second string so your conditions are not clear or your example is not proper
what you mean by "alpha"
Hello Sandy, sorry for the inconvenience. What i meant as alpha is Alphabets (A to Z). In my example. D4 has 2154-255 254B, 5545E, D454-D444 258BA454. In this data, B E D D are available as single alpha character where BA is double alpha character. Now i want to insert the alpha character from C4 that is A before each single alpha character in D4. The result would be 2154-255 254AB, 5545AE, AD454-AD444 258BA455. A is added before each single alpha character in the cell and double character BA remains same without any changes. Data is just an example, there would be 1000 characters with combination of Alpha, numeric and symbols. Hope this helps. Thank you.
 
Upvote 0
Hi, here is a UDF that you can try, let us know if you need help with adding the code.

VBA Code:
Function ReplaceSingleChar(s As String, rep As String) As String
Dim v As Variant, i As Long
For Each v In Split(s, " ")
    If Not v Like "*[A-Z][A-Z]*" Then
        If v Like "*[A-Z]*" Then
            For i = 65 To 90
                If InStr(1, v, Chr(i)) > 0 Then
                    v = Replace(v, Chr(i), rep & Chr(i))
                    Exit For
                End If
            Next i
        End If
    End If
ReplaceSingleChar = ReplaceSingleChar & " " & v
Next v
ReplaceSingleChar = Mid(ReplaceSingleChar, 2)
End Function

Here is how it can be used in the worksheet.

Book4
AB
1A2154-255 254B, 5545E, D454-D444 258BA454
22154-255 254AB, 5545AE, AD454-AD444 258BA454
Sheet1
Cell Formulas
RangeFormula
B2B2=ReplaceSingleChar(B1,A1)
 
Upvote 0
Solution
could you post a bigger example with different strings?
Here you go.

Book1
CD
4AA301. A305, A307, A309A310, A312, A314A315, A718A719, B001B002, B053, VB101VB112 VB114 B116B123, B125, B441B444, E101E103, F001F002, F061F062, K081K085, K087K095, K097K113, K141K148, N051N054, N056N062, N123, N153, N157, N191N198, N385, N388, N424, N541, N546N547, N635, N651N658, N751N752, N811N812, N931N936,A308 E064, E083E086, E141E152, F041F042, F101F102, F171, F180, K131K133, K161K164, L004L006, L751L752, L754, L806L807, L871L873 L891L900, N081N088, N093N099, N111N114, N115N117, N118N122, N124N127, N130N132, N133N137, N151, N154, N156, N158, N163N166, N168, N221N224, N234, N291N293, N307N310, N313N314, N372, N382N384 N386N387, N471N472, N672, N921
Sheet2
 
Upvote 0
T
Hi, here is a UDF that you can try, let us know if you need help with adding the code.

VBA Code:
Function ReplaceSingleChar(s As String, rep As String) As String
Dim v As Variant, i As Long
For Each v In Split(s, " ")
    If Not v Like "*[A-Z][A-Z]*" Then
        If v Like "*[A-Z]*" Then
            For i = 65 To 90
                If InStr(1, v, Chr(i)) > 0 Then
                    v = Replace(v, Chr(i), rep & Chr(i))
                    Exit For
                End If
            Next i
        End If
    End If
ReplaceSingleChar = ReplaceSingleChar & " " & v
Next v
ReplaceSingleChar = Mid(ReplaceSingleChar, 2)
End Function

Here is how it can be used in the worksheet.

Book4
AB
1A2154-255 254B, 5545E, D454-D444 258BA454
22154-255 254AB, 5545AE, AD454-AD444 258BA454
Sheet1
Cell Formulas
RangeFormula
B2B2=ReplaceSingleChar(B1,A1)
Thank you FormR, it is working as expected. Stay safe.
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

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
Back
Top