Preserving leading zero

[a]

Hello,

I'm certain that there should be a way to preserve a leading zero when
you don't know the length of the string - but I certainly don't know how.

I have a string like "010-056". If I put the variable in a watch window
- it comes up as a string. The problem is that I need to get rid of the
"-". When I do this, the string becomes a number.

Because the left character is not always a zero and, as I mentioned, the
length of the string varies, I cannot figure out how to keep the leading
zero intact when I replace the "-".

Does anybody have a solution to this?

Any help in this would be most appreciated.

Thanks much in advance.

Anita

 

[Guest]

You can format the cell as text, then whatever you type, it will display that
unchanged.

 

[Guest]

This is not a problem in VBA:


Sub gsnu()
Dim s As String
s = "010-056"
MsgBox (s)
s = Replace(s, "-", "")
MsgBox (s)
End Sub

In the worksheet it is a problem. Find/Replace will convert the string into
a number and drop the leading zero.

If the data is entered as:
'010-056
rather than
010-056
then Find/Replace will leave the leading zero alone. It is also very easy
to convert existing data from text into apostrophe'd text.

 

[Mike Fogleman]

You didn't mention how you got rid of the "-", but this seems to work for
me:

I have this in cell A1: 010-056

Sub test()
Dim x As String

x = Range("A1").Value
x = Replace(x, "-", "", , , vbTextCompare)
End Sub

Mike F

 

[a]

Davey,

Thanks for responding. I did format the column as text but that doesn't
seem to be enough. I've even tried using cstr but, as the text comes
from another sheet and then I replace the "-" with nothing, the cstr
won't work because of the timing. Once the "-" has been replaced, the
leading zero is gone.

For instance, the text is "003-500". It comes from a cell that is
formatted as text into another spreadsheet which is also formatted as
text. Once the "-" is replaced, it becomes a value even though the cell
formatting is still "text".

Any other thoughts?

Best Regards,
Anita

 

[a]

Gary's Student,

This looks like it should work but when I try it, I get the message "sub
or function not defined". If I do a debug - it points to "replace". Is
there something I have to do to get VBA to recogonize "Replace"?

Thanks to you, Davey Jones and Mike Fogelman for you help.

Regards,
Anita

 

[Guest]

Yeah, sorry. Never used replace with txt formatting. Use a custom format with
000000 as the format type. Think that'd do it...

 

[Guest]

I m sorry. REPLACE was introduced in Excel 2000. If you don't have REPLACE
then we can use the older form:


Sub gsnu2()
Dim s As String
s = "010-056"
MsgBox (s)
s = Application.Substitute(s, "-", "")
MsgBox (s)
End Sub

 

[a]

Thanks Gary's Student,

That did it. I'm working in an old version of Excel at home. I do have
2000 at work and so I'll try the other syntax when I get there.

Thanks also to DaveyJones and Mike Fogleman.

You folks are great!

Best Regards,
Anita