troubleshooting Question

Convert data type in Excel

Avatar of mcrmg
mcrmg asked on
VBA
9 Comments1 Solution7 ViewsLast Modified:
Hi,

I need to format the fields in Excel first before importing into SQL. the field in database is a string type.
In VBA, this is my code, what I am trying to do is to get rid of leading 0s if any. Then convert back to TEXT.

It works fine but I found a problem today. If the data has an ending character, then it removes the ending character.
For example: 123456B  ---> 123456
                       58796A   ---> 58796

What is the best way to just remove the leading 0s, and keep it as TEXT field. thanks

    '**************

    With Range("B2:B" & Cells(Rows.Count, 1).End(xlUp).Row)
    .NumberFormat = "General"
    .Value = .Value
    End With

    With Range("B2:B" & Cells(Rows.Count, 1).End(xlUp).Row)
    .NumberFormat = "@"
    .Value = .Value
    End With   
   '**************

Open in new window

ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros