Return two mid functions as one string.
 

I have this code which attempts to return parts of a
string, separated by a period:

For Each c In Range(RefEdit1.Value).Cells
c.Value = Str(Mid(c.Value, 5, 5) & "." & Mid(c.Value,
1, 2))

When I take out the & ".", I get what I expect (the fifth
through ninth character of the string, followed by the
first two characters of the same string). But when I have
the & "." in the code (my intention is to have a dot
(period) between the two sections of text), I get only the
first Mid function returned (characters five through
nine). Can anyone help me with why this is happening? I
thought that by enclosing my period in double quotes that
the program would undersand it to mean text, but
apparently it's considering it something else.

Thanks,

Keith Lorenzen

Return two mid functions as one string.
 

Works for me. The three strings should concatenate, including the ".". I
cannot find a problem in what you have posted. BTW, Str() returns the string
representation of a number, but Mid already returns a string, as do the
concatenation operators (&). Using Str coerces the string to a number then
returns the string representation of that number. That's fine if that is
what you want, but I thought you'd like to know that it can make a
difference.

compa
Dim v As String
v = "110000000"
Debug.Print Mid(v, 5, 5) & "." & Mid(v, 1, 2)
Debug.Print Str(Mid(v, 5, 5) & "." & Mid(v, 1, 2))


--
Bob Kilmer


"Keith Lorenzen" wrote in message
...
I have this code which attempts to return parts of a
string, separated by a period:

For Each c In Range(RefEdit1.Value).Cells
c.Value = Str(Mid(c.Value, 5, 5) & "." & Mid(c.Value,
1, 2))

When I take out the & ".", I get what I expect (the fifth
through ninth character of the string, followed by the
first two characters of the same string). But when I have
the & "." in the code (my intention is to have a dot
(period) between the two sections of text), I get only the
first Mid function returned (characters five through
nine). Can anyone help me with why this is happening? I
thought that by enclosing my period in double quotes that
the program would undersand it to mean text, but
apparently it's considering it something else.

Thanks,

Keith Lorenzen

Return two mid functions as one string.
 

Thanks so much for the good information. I just can't
understand why it won't work for me. What's interesting
is that any other character seems to return what I expect,
and if I surround the period with spaces I get what I
expect, but I don't want the period surrounded by spaces!
It's pretty late in the day. I'll try again tomorrow.

Thanks also for your info re Str function. What you say
makes sense to me. Originally I had just used the Dim
functions, but when I couldn't get what I wanted, I added
the Str in an attempt to get VBA to understand that I
wanted a string.

Thanks again.

Keith Lorenzen



-----Original Message-----
Works for me. The three strings should concatenate,
including the ".". I
cannot find a problem in what you have posted. BTW, Str()
returns the string
representation of a number, but Mid already returns a
string, as do the
concatenation operators (&). Using Str coerces the string
to a number then
returns the string representation of that number. That's
fine if that is
what you want, but I thought you'd like to know that it
can make a
difference.

compa
Dim v As String
v = "110000000"
Debug.Print Mid(v, 5, 5) & "." & Mid(v, 1, 2)
Debug.Print Str(Mid(v, 5, 5) & "." & Mid(v, 1, 2))


--
Bob Kilmer


"Keith Lorenzen" wrote in
message
...
I have this code which attempts to return parts of a
string, separated by a period:

For Each c In Range(RefEdit1.Value).Cells
c.Value = Str(Mid(c.Value, 5, 5) & "." & Mid
(c.Value,
1, 2))

When I take out the & ".", I get what I expect (the
fifth
through ninth character of the string, followed by the
first two characters of the same string). But when I
have
the & "." in the code (my intention is to have a dot
(period) between the two sections of text), I get only
the
first Mid function returned (characters five through
nine). Can anyone help me with why this is happening?
I
thought that by enclosing my period in double quotes
that
the program would undersand it to mean text, but
apparently it's considering it something else.

Thanks,

Keith Lorenzen



.

Return two mid functions as one string.
 

I just realized what's causing my problem. The second Mid
function is analyzing a part of a text string consisting
of two zeros. And the first part consists of another
number string. So it's coming up with Five digit number
followed by a period, followed by two zeroes, and so it's
leaving off the two zeroes because they're not relevant
zeroes. Do you know how I can extract these numeric
characters as strings/text, so that they will be returned
as text instead of values? I realize I'm using c.value,
but I don't know what the alternative is. c.text?

Please let me know if you have an idea.

Thanks so much.

Keith Lorenzen


-----Original Message-----
Works for me. The three strings should concatenate,
including the ".". I
cannot find a problem in what you have posted. BTW, Str()
returns the string
representation of a number, but Mid already returns a
string, as do the
concatenation operators (&). Using Str coerces the string
to a number then
returns the string representation of that number. That's
fine if that is
what you want, but I thought you'd like to know that it
can make a
difference.

compa
Dim v As String
v = "110000000"
Debug.Print Mid(v, 5, 5) & "." & Mid(v, 1, 2)
Debug.Print Str(Mid(v, 5, 5) & "." & Mid(v, 1, 2))


--
Bob Kilmer


"Keith Lorenzen" wrote in
message
...
I have this code which attempts to return parts of a
string, separated by a period:

For Each c In Range(RefEdit1.Value).Cells
c.Value = Str(Mid(c.Value, 5, 5) & "." & Mid
(c.Value,
1, 2))

When I take out the & ".", I get what I expect (the
fifth
through ninth character of the string, followed by the
first two characters of the same string). But when I
have
the & "." in the code (my intention is to have a dot
(period) between the two sections of text), I get only
the
first Mid function returned (characters five through
nine). Can anyone help me with why this is happening?
I
thought that by enclosing my period in double quotes
that
the program would undersand it to mean text, but
apparently it's considering it something else.

Thanks,

Keith Lorenzen



.

Return two mid functions as one string.
 

Keith,

If VBA doesn't understand that you want a string, you
could try using CStr instead of Str, CStr is very similar.
It's a conversion function, following is help for CStr

Returns for CStr

If expression is CStr returns
Boolean A string containing True or False
Date A string containing a date in the short date format of your system
Null A run-time error
Empty A zero-length string ("")
Error A string containing the word Error followed by the error number
Other numeric A string containing the number

Dan E

"Keith Lorenzen" wrote in message ...
Thanks so much for the good information. I just can't
understand why it won't work for me. What's interesting
is that any other character seems to return what I expect,
and if I surround the period with spaces I get what I
expect, but I don't want the period surrounded by spaces!
It's pretty late in the day. I'll try again tomorrow.

Thanks also for your info re Str function. What you say
makes sense to me. Originally I had just used the Dim
functions, but when I couldn't get what I wanted, I added
the Str in an attempt to get VBA to understand that I
wanted a string.

Thanks again.

Keith Lorenzen



-----Original Message-----
Works for me. The three strings should concatenate,
including the ".". I
cannot find a problem in what you have posted. BTW, Str()
returns the string
representation of a number, but Mid already returns a
string, as do the
concatenation operators (&). Using Str coerces the string
to a number then
returns the string representation of that number. That's
fine if that is
what you want, but I thought you'd like to know that it
can make a
difference.

compa
Dim v As String
v = "110000000"
Debug.Print Mid(v, 5, 5) & "." & Mid(v, 1, 2)
Debug.Print Str(Mid(v, 5, 5) & "." & Mid(v, 1, 2))


--
Bob Kilmer


"Keith Lorenzen" wrote in
message
...
I have this code which attempts to return parts of a
string, separated by a period:

For Each c In Range(RefEdit1.Value).Cells
c.Value = Str(Mid(c.Value, 5, 5) & "." & Mid
(c.Value,
1, 2))

When I take out the & ".", I get what I expect (the
fifth
through ninth character of the string, followed by the
first two characters of the same string). But when I
have
the & "." in the code (my intention is to have a dot
(period) between the two sections of text), I get only
the
first Mid function returned (characters five through
nine). Can anyone help me with why this is happening?
I
thought that by enclosing my period in double quotes
that
the program would undersand it to mean text, but
apparently it's considering it something else.

Thanks,

Keith Lorenzen



.

Return two mid functions as one string.
 

Keith,

It appears the string is being coerced while it is being constructed. The
inline formula you are using isn't always a string value.

1) I think you need to explicitly declare the variable types and build the
string with these declared variables. It is extremely important that ALL of
the parts be String types. The "sTemp3 = " line is the key step in the
process. The string can't be allowed to be coerced.

2) I assume you want the cell entry to be a "text" value. You need to help
Excel with this, otherwise any cell value ending in ".00", for example, will
lose the zero ending. I accomplish this by adding a single quote to the
front of the string. You will need to set the text alignment of the cell on
the worksheet to your desired type (left, right, centered).

Try this modified version of your code. Be sure to include the Dim
statements. If you use the VBE Locals Window view during debug mode, you can
watch how the strings are being manipulated. Hopefully this works for your
data.

Troy


Dim c As Range
Dim sPart1 As String
Dim sPart2 As String
Dim sTemp3 As String
Dim sTemp4 As String

For Each c In Range(RefEdit1.Value).cells
sPart1 = Mid(c.Value, 5, 5)
sPart2 = Mid(c.Value, 1, 2)
sTemp3 = sPart1 & "." & sPart2
sTemp4 = "'" & sTemp3 '''<=== Adding a single quote to the
front.

c.Value = sTemp4
Next


"Keith Lorenzen" wrote in message
...
I just realized what's causing my problem. The second Mid
function is analyzing a part of a text string consisting
of two zeros. And the first part consists of another
number string. So it's coming up with Five digit number
followed by a period, followed by two zeroes, and so it's
leaving off the two zeroes because they're not relevant
zeroes. Do you know how I can extract these numeric
characters as strings/text, so that they will be returned
as text instead of values? I realize I'm using c.value,
but I don't know what the alternative is. c.text?

Please let me know if you have an idea.

Thanks so much.

Keith Lorenzen


-----Original Message-----
Works for me. The three strings should concatenate,
including the ".". I
cannot find a problem in what you have posted. BTW, Str()
returns the string
representation of a number, but Mid already returns a
string, as do the
concatenation operators (&). Using Str coerces the string
to a number then
returns the string representation of that number. That's
fine if that is
what you want, but I thought you'd like to know that it
can make a
difference.

compa
Dim v As String
v = "110000000"
Debug.Print Mid(v, 5, 5) & "." & Mid(v, 1, 2)
Debug.Print Str(Mid(v, 5, 5) & "." & Mid(v, 1, 2))


--
Bob Kilmer


"Keith Lorenzen" wrote in
message
...
I have this code which attempts to return parts of a
string, separated by a period:

For Each c In Range(RefEdit1.Value).Cells
c.Value = Str(Mid(c.Value, 5, 5) & "." & Mid
(c.Value,
1, 2))

When I take out the & ".", I get what I expect (the
fifth
through ninth character of the string, followed by the
first two characters of the same string). But when I
have
the & "." in the code (my intention is to have a dot
(period) between the two sections of text), I get only
the
first Mid function returned (characters five through
nine). Can anyone help me with why this is happening?
I
thought that by enclosing my period in double quotes
that
the program would undersand it to mean text, but
apparently it's considering it something else.

Thanks,

Keith Lorenzen



.

Return two mid functions as one string.
 

Thanks, Dan. This is good info. I didn't know about
CStr, but it looks like it would work for me.

Keith Lorenzen
-----Original Message-----
Keith,

If VBA doesn't understand that you want a string, you
could try using CStr instead of Str, CStr is very similar.
It's a conversion function, following is help for CStr

Returns for CStr

If expression is CStr returns
Boolean A string containing True or
False
Date A string containing a date in
the short date format of your system
Null A run-time error
Empty A zero-length string ("")
Error A string containing the word
Error followed by the error number
Other numeric A string containing the number

Dan E

"Keith Lorenzen" wrote in
message ...
Thanks so much for the good information. I just can't
understand why it won't work for me. What's interesting
is that any other character seems to return what I
expect,
and if I surround the period with spaces I get what I
expect, but I don't want the period surrounded by
spaces!
It's pretty late in the day. I'll try again tomorrow.

Thanks also for your info re Str function. What you say
makes sense to me. Originally I had just used the Dim
functions, but when I couldn't get what I wanted, I
added
the Str in an attempt to get VBA to understand that I
wanted a string.

Thanks again.

Keith Lorenzen



-----Original Message-----
Works for me. The three strings should concatenate,
including the ".". I
cannot find a problem in what you have posted. BTW, Str
()
returns the string
representation of a number, but Mid already returns a
string, as do the
concatenation operators (&). Using Str coerces the
string
to a number then
returns the string representation of that number.
That's
fine if that is
what you want, but I thought you'd like to know that it
can make a
difference.

compa
Dim v As String
v = "110000000"
Debug.Print Mid(v, 5, 5) & "." & Mid(v, 1, 2)
Debug.Print Str(Mid(v, 5, 5) & "." & Mid(v, 1, 2))


--
Bob Kilmer


"Keith Lorenzen" wrote in
message
...
I have this code which attempts to return parts of a
string, separated by a period:

For Each c In Range(RefEdit1.Value).Cells
c.Value = Str(Mid(c.Value, 5, 5) & "." & Mid
(c.Value,
1, 2))

When I take out the & ".", I get what I expect (the
fifth
through ninth character of the string, followed by
the
first two characters of the same string). But when I
have
the & "." in the code (my intention is to have a dot
(period) between the two sections of text), I get
only
the
first Mid function returned (characters five through
nine). Can anyone help me with why this is
happening?
I
thought that by enclosing my period in double quotes
that
the program would undersand it to mean text, but
apparently it's considering it something else.

Thanks,

Keith Lorenzen



.



.