Return two mid functions as one string.

  • Thread starter Thread starter Keith Lorenzen
  • Start date Start date
K

Keith Lorenzen

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
(e-mail address removed)
 
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.

compare:
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))
 
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
(e-mail address removed)
 
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
(e-mail address removed)
 
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,

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
 
Thanks so much for this good information. I am going to
look at your suggestion and hopefully can learn something
from it. I was able to solve my problem ealier in another
way. Here is my final code if anyone is interested:

Dim MyDot As String
Dim MyFirstPart As String
Dim MySecondPart As String


For Each C In Range(RefEdit1.Value).Cells
MyDot = "."
MyFirstPart = Mid(C.Text, 5, 5)
MySecondPart = Mid(C.Text, 1, 2)

C.NumberFormat = "@" 'This line is required as well
as the Format function below to prevent the trailing
zeroes from being removed.

C.Value = Format(MyFirstPart & MyDot &
MySecondPart, "@")

Next

Thanks to everyone for your great suggestions and help. I
really appreciate it.

Keith Lorenzen
(e-mail address removed)
 
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
Click to expand...
the short date format of your system
Null A run-time error
Empty A zero-length string ("")
Error A string containing the word
Click to expand...
Error followed by the error number
Other numeric A string containing the number

Dan E

"Keith Lorenzen" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
 
Back
Top