Convert text to numbers

[sms2luv]

I have employees attendance data with their Emp I'd and Name.
Emp I'd for some emp are just numbers like 123456, but for some agents it's UK7432.
When I extract data from the website, I get a green arrow on Emp I'd with numbers.
When I do vlookup for emp I'd, I do not get the results.
I have to manually multiply the emp I'd with number 1 to make it a number and then Vlookup works.
I tried formating to number but it didn't work

Request you to please provide a code for multiply the emp I'd with number 1.
As the rows are not certain, I mean it can be 10 or 1000 depending on the emp data, how can I loop through to multiple emp id rill the last row

 

[Luke M]

Try this; uses the power of Paste Special - Multiply.

Sub ConvertText()
    Dim rngMagic As Range
    Dim lastRow As Long
    'What column has EMP ID?
    Const myCol As String = "A"
   
   
   
    Application.ScreenUpdating = False
   
    With ActiveSheet
        'This is a blank cell we'll use temporarily
        Set rngMagic = Range("Z1")
       
        rngMagic.Value = 1
       
        lastRow = .Cells(.Rows.Count, myCol).End(xlUp).Row
       
        rngMagic.Copy
       
        .Range(.Cells(1, myCol), .Cells(lastRow, myCol)).PasteSpecial xlPasteValues, xlPasteSpecialOperationMultiply
    End With
    rngMagic.Clear
   
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
       
End Sub

 

[NARAYANK991]

Hi ,

If your list of employee IDs is a mix of digits and letters , then surely the correct way would be to use all of them as text , rather than convert only those IDs which consist solely of digits to numbers ?

Narayan

 

[neeraj2050]

Try if this work :

Select that column - Data - Text to Column - Next - Next - Finish

It will automatically change the format

Hope it helps!!

Regards
Neeraj Agarwal