Replace cell content in Excel with array content

Question

I have an Excel worksheet which includes a text column which stores both text and numbers. I am trying to extract serial numbers, which are 13 digits long, and replace the column content with these serial numbers. I was able to extract the serial numbers but I am stalling on how to replace the cell.value with the serial numbers. Below is my initial approach:

Sub extract_digits()
Dim cell As Range
Dim arr As Variant, arrElem As Variant
Dim final_arr As Variant
With Worksheets("Test_1")
    For Each cell In .Range("H5", .Cells(.Rows.Count, "H").End(xlUp))
        arr = Split(Replace(cell.Value, "  ", " "), " ") 
        For Each arrElem In arr
            If Len(arrElem) = 13 Then MsgBox arrElem
            
        Next arrElem
    
    Next cell
End With

End Sub

Instead of displaying a message box, you can use cell.value=arrElem — YasserKhalil
– YasserKhalil, Commented Sep 17, 2021 at 21:34

ACCtionMan · Accepted Answer · 2021-09-18 03:54:22Z

1

The second option (Remove Letters From Strings/Numbers/Cells With User Defined Function) from this site should do the trick:

https://www.extendoffice.com/documents/excel/3244-excel-remove-letters-from-strings-cells-numbers.html

Function below is from above website, it's not mine

Function StripChar(Txt As String) As String
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "\D"
StripChar = .Replace(Txt, "")
End With
End Function

How is this working

Is using Regular Expressions (RegEx). The .Pattern means it's looking for any non numeric (ie 0 to 9), it's then using the .Replace function to replace any non numeric with nothing.

How do you use this

Option 1 You can either use it directly in a column next to your column, eg =StripChar(H5) and then just copy that formula down.

Option 2 Alternatively using your code

Sub extract_digits()
Dim cell As Range
Dim arr As Variant, arrElem As Variant
Dim final_arr As Variant
With Worksheets("Test_1")
    For Each cell In .Range("H5", .Cells(.Rows.Count, "H").End(xlUp))
        cell = StripChar(cell.Value)
    
    Next cell
End With

End Sub

edited Sep 18, 2021 at 3:54

answered Sep 18, 2021 at 3:45

ACCtionMan

5111 gold badge4 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

PythonLearner Over a year ago

I guess I would need to change the pattern in the function StripChar as my cell contains all kind of text to include other numbers; I would only need 13 digits numbers. @ACCtionMan

PythonLearner Over a year ago

A negative lookahead completed the function to meet my needs.

Function StripChar(Txt As String) As String With CreateObject("VBScript.RegExp") .Global = True .Pattern = "(?!\b\d{13}\b)(\b\w+\b)" StripChar = .Replace(Txt, "+") End With End Function

Collectives™ on Stack Overflow

Replace cell content in Excel with array content

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related