Solved

# Return only characters left of first space

Posted on 2011-03-01
438 Views
I have been all over EE to try to figure this out and I can't. I know this is super easy to do so I apologize for even asking.

Basically, I have a scale hooked up to a com port; you push the print button on the scale and, with the help of WinWedge, it puts whatever the scale reads into whatever active application you want. Problem is that it sends too much info from the scale. I get a string of  "1.034 lb g" as my string. How do I do a Trim(Left) or whatever to return just the weight portion? Since I do a calcuation with the weight I need to lop off the text.

Sorry I have no example to start with as I have no idea what I am doing. Also, if there is a 'good' website that can instruct me on this I would appreciate the link. I have EE'ed and googled extensively and can't find the exact answer. Thanks for any input you can give me.
0
Question by:G Scott
• 7
• 4
• 2
• +3

LVL 28

Assisted Solution

omgang earned 62 total points
Left("1.034 lb g", InStr("1.034 lb g", " ")) will return "1.034"

To convery to number you can
CDbl(Left("1.034 lb g", InStr("1.034 lb g", " "))) = 1.034

OM Gang
0

LVL 28

Expert Comment

In Access VBA Help search for
String functions
quite a bit of information there.
OM Gang
0

LVL 95

Expert Comment

Mystring = left(string, instr(string," "))

Instr tells you where in the string the first instance of the following string appears.
0

LVL 28

Expert Comment

Actually, I left a space in there.  Should be
Left("1.034 lb g", InStr("1.034 lb g", " ") - 1) will return "1.034"

OM Gang
0

LVL 47

Expert Comment

if you can store this value as a string, or in a field, you can parse the part to the left of the first space using the Left( ) function, like:

strValue = "1.034 lb g"
Left(strValue, instr(strValue, " ") - 1)

The problem with this is that if there is not space, then instr(strValue, " ") will return 0, and you cannot take the left -1 (0-1) characters of strValue.

another way to approach it is with the Split function which parses a string based on a delimeter, in this case the space, and returns an array.

varArray = split(strArray, " ")

But you don't have to actually have to save the entire array, you can refer to an element of the array like:

?split(strValue, " ")(0)

Hope this helps
0

LVL 37

Expert Comment

You can use

SPLIT("1.034 lb g")(0)

0

LVL 1

Author Comment

Ok, when I do this:

``````Dim myString As String
myString = Left("1034 lb f", InStr("1034 lb f", " ") - 1)
MsgBox myString
``````

the MsgBox says "1034". Just like I want. However, when I do this:

``````Dim myString As String
myString = Left(Me.Text0, InStr(Me.Text0, " ") - 1)
MsgBox myString
``````

I even tried this to no avail:

``````Dim myString As String
myString = Left(" & Me.text0 & ", InStr(" & Me.text0 & ", " ") - 1)

MsgBox myString
``````
0

LVL 28

Expert Comment

What happens when you
Debug.Print Me.Text0
???
0

LVL 28

Expert Comment

Or
MsgBox Me.Text0
OM Gang
0

LVL 1

Author Comment

Ok. so it appears that the character between the weight and the "lb" is not a space. When I changed the code to:

myString = Left(" & Me.text0 & ", InStr(" & Me.text0 & ", ".") - 1)

It returned a "2", So I changed it to:

myString = Left(" & Me.text0 & ", InStr(" & Me.text0 & ", "l") - 1)

and it worked.
0

LVL 37

Expert Comment

Dim myString As String
myString = SPLIT(me.Text0)(0)
MsgBox myString
0

LVL 1

Author Comment

Here is the text directly from the printer:

2.240 lb  G

How can I tell that there is no space character in there?
0

LVL 28

Expert Comment

Dim strASCII As String
Dim i As Integer

For i = 1 to Len(Me.Text0)
strASCII = strASCII & Asc(Mid(Me.Text0, i, 1)) & ";"
Next

MsgBox strASCII

This will display a semicolon seperated list of the ASCII character codes for the string
OM Gang
0

LVL 119

Accepted Solution

Rey Obrero earned 63 total points
try using the val() function if you are just after the numeric values

val("1.034 lb g")  > 1.034

val(" 1.034 lb g")  > 1.034
0

LVL 28

Expert Comment

<<try using the val() function if you are just after the numeric values>>

Nahh, that's too easy; not enough lines of code............
How come I didn't think of that?
OM Gang
0

LVL 1

Author Closing Comment

Thanks for your help everyone!! It's working great.
0

## Featured Post

### Suggested Solutions

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.