Hi,

It must be a piece of cake but I can't figure out how to do this:

Current Code:
Range(("A2"), Range("B65536").End(xlUp)).Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart

Current Problem:
I have numbers in a cell with leading zero's and spaces at the end. I need to remove the blanks at the end but keep the zeros in the front. When I remove the spaces, zero's go away. Formating to text doesn't work as zeros still disappear.

My solution:
I need the code to find the first value of a cell and if its 0, replace with ' and 0...

Please help... :)

Thanks...

Well you'll have to check each cell otherwise you won't be able to detect a cell starting with 0.

try :-
dim lnX as long, rCell as range
lnx = row(Range("B" & activesheet.rows.count).end(xlup)
for each rcell in range("A2:B" & lnX)
if left(rcell.value,1) = "0" then rcell.value = "'" & rcell.value
if left(rcell.value,2) = " 0" then rcell.value = "'" & rcell.value ' to be sure
rcell.value = replace(rcell.value," ","")
next rcell

Just to satisfy my curiosity and help over come my ignorance, how does one get leading 0 in a cell without having ' at the start?

Thanks
Brian

if it's formatted as text

acutally the way i get the data is as follows:
there can be multiple spaces in the quotes...

="392094 "

The above given solution will replace all " " (spaces) with "" (nothing) so it should cope with your data.