Identify and substitute text pattern (LnnnnL)

[TheTallBloke]

Hello folks,

Bit of a strange one - I am working on correcting data inconsistencies in model numbers sent by an incorigible supplier.
The goal is finding the following string within a cell and appending the letter X at the end:

(letter)(4 consecutive numbers)(letter in defined list - [E,V]) -> should get the letter X appended


I would like to do this using a formula rather than a macro - for control/ease of use reasons.
Example attached - apologies for using dummy data, confidentiality is the reason.

[TABLE="width: 363"]
<tbody>[TR]
[TD]ABC-DEF1234EX 1.2.3.4[/TD]
[TD]correct[/TD]
[/TR]
[TR]
[TD]ABC-DEF1234E 5.2.3.4[/TD]
[TD]should be ABC-DEF1234EX 1.2.3.4[/TD]
[/TR]
[TR]
[TD]DGH4321V 1.2.3-4[/TD]
[TD]should be DGH4321VX 1.2.3-4[/TD]
[/TR]
[TR]
[TD]ABCDD-1114X[/TD]
[TD]should be ignored[/TD]
[/TR]
</tbody>[/TABLE]


Possible or impossible? Thanks.

 

[oldbrewer]

DGH4321V 1.2.3-4

how is this (letter) (4 consecnumbers) (letter E thru V)

is it any number of consec letters at start ?

 

[TheTallBloke]

DGH4321V 1.2.3-4

how is this (letter) (4 consecnumbers) (letter E thru V)

is it any number of consec letters at start ?

I am looking for a group of 4 numbers that is preceded by one or more letters and succeeded by either E or V - if the next character after this string is not X then X should be inserted after the string.
The whole thing is not always at the beginning of the cell, sometimes there are other groups of letters in front of it.

Is this enough information please?

 

[RoryA]

Is a function written in VBA ok?

 

[Peter_SSs]

1. In your second example, why has the final section changed from 5.2.3.4 to 1.2.3.4?

2. I'm not sure of the difference between "correct" and "should be ignored" so exactly what should appear in the result column for the first and last examples?

3. Is it possible for a letter-4 digits-E/V combination to occur more than once in a cell?
eg ABC1234E RTR B9876VX F5555V G6H7.3.2.5

 

[TheTallBloke]

@RoryA - I'd like that to be the final option to be honest - I'd like to distribute this to a number of members of staff and the potential for messing up with VBA is higher.

@Peter_SSs

1. Sorry, that's just me being silly with the example data, the first number should not be modified.
2. Correct means the pattern is being recognised but the 'X' bit already exists, 'should be ignored' just means the pattern is not present.
3. No, one occurrence max.


Many thanks.

 

[Peter_SSs]

2. Correct means the pattern is being recognised but the 'X' bit already exists, 'should be ignored' just means the pattern is not present.

OK, but what about the second half of my point 2?

2.... so exactly what should appear in the result column for the first and last examples?

4. I suspect that this will require vba in some form, but just in case something comes to mind, what version of Excel are you using?

 

[Peter_SSs]

Some assumptions ...

a) The answer to the second part of my Q2 is "just repeat what is in column A on that row"
b) You are using an Excel version that has the CONCAT function (eg Excel through Office 365)
c) The symbols "#" and "@" will not occur in your column A values (though these could easily be substituted)
d) The letters in your data are all upper case like your samples (though we could amend to account for upper/lower)

IF at least b) is correct then this is possible with formula but you will have to be the judge about the following ...

I would like to do this using a formula rather than a macro - for control/ease of use reasons.

... as you have the choice from my options of

i) This small piece of vba code** for a user-defined function and the very simple formula shown in B1 below

Function Append(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]\d{4}[EV])(?=[^X]|$)"
  Append = RX.Replace(s, "$1X")
End Function

or

ii) The massive formula shown in C1 below.
This is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Excel Workbook
	A	B	C
1	ABC-DEF1234EX 1.2.3.4	ABC-DEF1234EX 1.2.3.4	ABC-DEF1234EX 1.2.3.4
2	ABC-DEF1234E 1.2.3.4	ABC-DEF1234EX 1.2.3.4	ABC-DEF1234EX 1.2.3.4
3	DGH4321V 1.2.3-4	DGH4321VX 1.2.3-4	DGH4321VX 1.2.3-4
4	ABCDD-1114X	ABCDD-1114X	ABCDD-1114X
Append X

** To implement the user-defined function code ..
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 above 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 and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)