Vba: copy and past numbers stored as text

[Nelson78]

Hello everybody.

I have a huge amount of records in which a lot of number are stored as text.

In order to simplify, I have created the above image:

https://imgur.com/a/H5IXsEm


The task is the following: for each value in colomn E, put in column F the related value as reported in column B, keeping the format "Number stored as text", if any.

I have tried with a Application.VLookup approach, but it doesn't allow to keep the format.

How can I figure this out? (probably with a copy and paste approach)

In the example I would like:
in F2 3924994 (see B5)
in F3 3473434 (see B12)
in F4 0117509 (see B15) stored as text because in B15 it is stored as text.

Thank's.

 

[Nine Zero]

try adding this

.NumberFormat = "@"
'so something like
Stuff = Application.WorksheetFunction.Vlookup(blah blah blah)
Stuff.NumberFormat = "@"

 

[Rick Rothstein]

Give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub VLookupTextNumbers()
  Dim R As Long, Table As Range, Data As Variant, Result As Variant
  Set Table = Range("A2", Cells(Rows.Count, "B").End(xlUp))
  Data = Range("E2", Cells(Rows.Count, "E").End(xlUp))
  ReDim Result(1 To UBound(Data), 1 To 1)
  For R = 1 To UBound(Data)
    Result(R, 1) = CStr(Application.VLookup(Data(R, 1), Table, 2, False))
  Next
  With Range("F2").Resize(UBound(Result))
    .NumberFormat = "@"
    .Value = Result
  End With
End Sub[/td]
[/tr]
[/table]

 

[Nelson78]

Well, it works perfectly.

Thank's.

Give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub VLookupTextNumbers()
  Dim R As Long, Table As Range, Data As Variant, Result As Variant
  Set Table = Range("A2", Cells(Rows.Count, "B").End(xlUp))
  Data = Range("E2", Cells(Rows.Count, "E").End(xlUp))
  ReDim Result(1 To UBound(Data), 1 To 1)
  For R = 1 To UBound(Data)
    Result(R, 1) = CStr(Application.VLookup(Data(R, 1), Table, 2, False))
  Next
  With Range("F2").Resize(UBound(Result))
    .NumberFormat = "@"
    .Value = Result
  End With
End Sub[/td]
[/tr]
[/table]