Find cells that begin with a number, and add text to them

[mlarson]

Hi there, hoping you guys can help me with this. Thanks in advance for your help!

I would like for any cell in my spreadsheet that begins with a number to have XXX added to the beginning of that cell. For example...
123abc becomes XXX123abc
abc stays abc
4idiayd becomes XXX4idiayd
98a72ksh becomes XXX98a72ksh
cheeseburger stays cheeseburger

Hopefully I explained this clearly. Thanks!

 

[Strithken]

Let me know if that works for you.

Sub ConcatXXXtoValueIfFirstCharacterNumeric()
    Dim sheet as Worksheet: Set sheet = ActiveSheet
    Dim arr as Variant: arr = sheet.UsedRange.value

    Dim rowNum as Long
    Dim colNum as Integer

    For rowNum = LBound(arr, 1) to UBound(arr, 1)
        For colNum = LBound(arr, 2) to UBound(arr, 2)
            If IsNumeric(Left(arr(rowNum, colNum), 1)) Then
                arr(rowNum, colNum) = “XXX” & arr(rowNum, colNum)
            End If
        Next colNum
    Next rowNum
    sheet.UsedRange.value = arr
End Sub

 

[mlarson]

Thanks so much, very helpful! As an aside, I wonder if there's a way to do it without VBA?

 

[Strithken]

Thanks so much, very helpful! As an aside, I wonder if there's a way to do it without VBA?

Is all your data in a single column or in miscellaneous columns and rows throughout the sheet?

 

[mlarson]

Many columns and rows throughout the sheet.

 

[mlarson]

A workaround that would be useful is if there is a formula in C5 that would essentially say "if the first character of A5 is a number, return XXX in C5, if not, return A5 in C5" - if there is a way to do that formula I could make it work for what I'm trying to accomplish. Thanks!

 

[Strithken]

A workaround that would be useful is if there is a formula in C5 that would essentially say "if the first character of A5 is a number, return XXX in C5, if not, return A5 in C5" - if there is a way to do that formula I could make it work for what I'm trying to accomplish. Thanks!

Oh, that's simpler than what I was trying to do.
=IF(ISNUMBER(LEFT(A5,1)),"XXX" & A5,A5)

 

[mlarson]

Thank you very much!