2 replies to this topic

[stackemevs]

Hi,

I currently have a very long column (5,000+) of which a lot of cells start with a range of zeros which I want to get rid of.

At the moment I use =SUBSTITUE(A1,"00000","",). It works but I then have to copy the command down to all cells.

I have been thinking of something like =VBLOOKUP(*0000,A1:A5000,1,False), but not been able to get it to work.

I think there should be a way to use VBLOOKUP as a replace tool, any suggestions please. Also just had a thought and off to try it, can I put a range within Substitue so it goes through and does all rather than copying down?

Thanks

[stackemevs]

I could of course also do something like =REPLACE(A1,1,5,""), but again believe would need copy for each row.

[stackemevs]

Not finding any potential answers with the VLookup, so looking into putting the Substitue into a Loop.

Something like

Do Until wkbFileIn.AtEndOfStream or

For nRow (having declared nRow as Long)
nRow = nRow+1
Loop

Issue really is the size of the file (5,000+ rows), so the answer is the most effective method to process this. Thanks