Preserving leading zero
 

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

Preserving leading zero
 

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


--
Dave


"a" wrote:

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

Preserving leading zero
 

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.
--
Gary's Student


"a" wrote:

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

Preserving leading zero
 

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" wrote in message
link.net...
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

Preserving leading zero
 

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

DaveyJones wrote:

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

Preserving leading zero
 

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

Gary''s Student wrote:

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.

Preserving leading zero
 

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


"a" wrote:

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

DaveyJones wrote:

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

Preserving leading zero
 

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
--
Gary''s Student


"a" wrote:

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

Gary''s Student wrote:

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.

Preserving leading zero
 

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

Gary''s Student wrote:

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