Formatting cell with unique format while having different inputs

[Vincent88]

How and what is the best way to unify the inputs of a cell (ie "A2") : There will probably be three type of inputs
123456
req%123456
req000000123456
but I want to this cell ("A2") to turn these inputS to one single format REQ000000123456 (add REQ000000 to the last six digits of input)
I only know to formate 123456 in FORMAT CELLS / CUSTOM but no knowledge to tackle the other two !
Should I create a marco for this ?

 

[Vincent88]

It shown error when I input prefixbefore the numbers. Need to change the script .Pattern = "[^1-9]"

 

[Vincent88]

tried to change it to CONCATENATE("req"&"[^1-9]") but not workable

 

[maabadi]

What is data validation restriction. It isn't related to macro?

 

[Vincent88]

What is data validation restriction. It isn't related to macro?

I forgot to remove the data validation. Sorry for misleading.

 

[Vincent88]

Hi Maabadi,
If I just want the code applies to the dynamic range in column A not the whole column, what is the change ?

 

[maabadi]

This is applied on Dynamic range. From A2 ro last row has data at column A. Not Total Column A.

 

[Vincent88]

This is applied on Dynamic range. From A2 ro last row has data at column A. Not Total Column A.

Hi maabadi,
There is error. When I input something outside the dynamic column. The debug shows TARGET. VALUE=TARGET.VALUE*1. And when I go into dynamic column and input something like req%112233, it won't convert it to REQ000000112233.

 

[maabadi]

Change

Lr = Range("A" & Rows.Count).End(xlUp).Row

To

Lr = Range("A2").End(xldown).Row

 

[Vincent88]

Hi maabadi,
Having several tests, I found this code is more workable and seems compatiable with other functions in WORKBOOK_SHEETCHANGE. But how to modify the code applies to the dynamic range in column A not the whole column ? I attach my file for easy debug purpose. Please help !
problemed file

If Target.Column = 1 Then
    Dim s As String
    Dim arr As Variant

    s = Target.Value
    If s = "" Then
      Target.NumberFormat = "General"
    Else
      With CreateObject("vbscript.regexp")
        .Pattern = "[^0-9]"
        .Global = True
        .IgnoreCase = True
        arr = Split(Application.Trim(.Replace(s, " ")), " ")
      End With
      Target.Value = arr
      Target.Value = Target.Value * 1
      Target.NumberFormat = """REQ0000000""General"
    End If
  End If

 

[maabadi]

Try this:

   Dim Lr As Long
   Lr = Range("A" & Rows.Count).End(xlUp).Row
If Interect(Target, Range("A2:A" & Lr)) Is Nothing Then Exit Sub
    Dim s As String
    Dim arr As Variant

    s = Target.Value
    If s = "" Then
      Target.NumberFormat = "General"
    Else
      With CreateObject("vbscript.regexp")
        .Pattern = "[^0-9]"
        .Global = True
        .IgnoreCase = True
        arr = Split(Application.Trim(.Replace(s, " ")), " ")
      End With
      Target.Value = arr
      Target.Value = Target.Value * 1
      Target.NumberFormat = """REQ0000000""General"
    End If