Find number and add ' to begining

[JonathanA]

Whether there is a better way to deal with this I don't know but I have a file sent to me every week that I cannot alter or affect the format it comes in. It has a list of products some which are all numeric but most have a Letter prefix. I need to have all the numbers as text even if they are a number if you see what I mean.
My current solution (not really a solution) is to go into the file and replace say 12948 with '12948 and format to general, there are not too many of these and it's not a disaster but ideally I would love to have a macro do this for me, the numbers are always in the same column "B", from row 2 to unknown (end of file), additionally there are a couple of products that have a leading zero so I would need to replace 5252 with '05252, although these numbers can be specifically mentioned in the code as there are only a handfull of them.

I would guess I need to use "For each Cell in Range"
and some kind of replace function with an isnumeric test but that's as far as I have got, I haven't really got a clue where to start with the syntax.
Any tips/ideas greatly appreciated.

 

[dafan]

i = 0
Do
    i = i + 1
        
    If Len(Cells(i, 2).Value) = 4 Then
        Cells(i, 2).Value = "'0" & Cells(i, 2).Value
    Else
        Cells(i, 2).Value = "'" & Cells(i, 2).Value
    End If
Loop Until Cells(i + 1, 2).Value = ""

 

[VoG]

Or

Sub NoToChr()
Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("B" & i)
        If IsNumeric(.Value) Then .Value = "'" & .Value
    End With
Next i
End Sub

 

[Stormseed]

Just curious to know, is it that you want all the numeric data to display or precede a zero before any number in your column ?

 

[dafan]

VoG remember there are some numbers with 4 digits as Excel trimmed them of the preceding 0.

 

[VoG]

VoG remember there are some numbers with 4 digits as Excel trimmed them of the preceding 0.

Yep - my code doesn't address the leading zeros issue as I thought that only applied to a couple of specific codes.

 

[JonathanA]

Thanks for all the speedy responses.

No, there are about 5 products with a leading zero that always gets lost in the transfer which I can specify, 5252 (should be '05252), 5253,5254,5253D,3463. The rest of the numbers just require a ' adding to them.

 

[VoG]

Try

Sub NoToChr()
Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("B" & i)
        If .Value = 5252 Or .Value = 5353 Or .Value = 5254 Or .Value = "5253D" Or .Value = 3463 Then
            .Value = "'0" & .Value
        ElseIf IsNumeric(.Value) Then
            .Value = "'" & .Value
        End If
    End With
Next i
End Sub

 

[JonathanA]

That works perfectly VoG, thanks very much.

Doing SQL for too long has withered my Excel VBA brain.