VBA Format Cell based on number sets

[jamesdean1379]

Having some issues here.

I have a report that i pull multiple times a day and in Column L some cells have no data and others may have anywhere from 4 - 20 numbers. I have to go into the cells with the numbers and separate them by every fourth number and add UN in front of them.

Example

BEFORE
Cell L2
145689732148

AFTER
Cell L2
UN1456, UN8973, UN2148

Some cells in column L could be blank so i need it to keep the blank cells. Also, as i said, there could be 4 or 20 numbers in that one cell. I know lastrow could vary but could be set around 5,000.

Can anyone help with this?

 

[BQardi]

Is VBA okay?
This would do what you want in the range L2 to L(where ever lastrow is):

Sub ConvertAllUN() 'Run this sub to convert all cells in L2:L???
Dim Cell As Range
Application.ScreenUpdating = False
For Each Cell In Range("L2:L" & Range("L" & Rows.Count).End(xlUp).Row)
    If Not Cell.Value = "" Then Cell.Value = ConvertToUN(Cell.Value)
Next Cell
End Sub

Function ConvertToUN(SrcValue As String) As String
Dim i As Long, UNValue As String, LenSrcValue As Long
LenSrcValue = Len(SrcValue)
For i = 4 To Len(SrcValue) Step 4
    UNValue = UNValue & "UN" & Mid(SrcValue, i - 3, 4) & ", "
Next i
ConvertToUN = Left(UNValue, Len(UNValue) - 2)
End Function

 

[jamesdean1379]

When i run this, i keep getting a debug error at the line "Run-Time error '1004' Range of object global failed

For Each Cell In Range("L2:L2000" & Range("L" & Rows.Count).End(xlUp).Row)

 

[BQardi]

Get rid of the "2000", the "Range("L" & Rows.Count).End(xlUp).Row" part will automatically find the last used row in column L

For Each Cell In Range("L2:L[COLOR=#ff0000]2000[/COLOR]" & [COLOR=#0000ff]Range("L" & Rows.Count).End(xlUp).Row[/COLOR])

 

[jamesdean1379]

of course. thanks !!! works perfectly

 

[BQardi]

You're welcome, hope it makes your life easier with your daily "pulling" of reports