Extracting part of a string

[Guest]

I have a line of text that reads: 52G 2%. Is there a formula that I can
write that will extract everything in front of the "G"? Sometimes the answer
may be 1 digit and at other times the answer may be 2 digits. For instance,
the line of text could be: 5G 14%. I'd like to right this directly into
the control. Thanks...

 

[Gunnar Nygaard]

I have a line of text that reads: 52G 2%. Is there a formula that I can
write that will extract everything in front of the "G"? Sometimes the
answer
may be 1 digit and at other times the answer may be 2 digits. For
instance,
the line of text could be: 5G 14%. I'd like to right this directly into
the control. Thanks...

Is it always an integer-expression preceeding the "G"?
Are there other Gs in the string?

This seems to work (though there may be more smooth ways to fix it):

Function Test01()
Dim i As Integer
Dim strTest As String
Dim iResult As Long
strTest = "2345343G 34%"
For i = 0 To Len(strTest) - 1
If IsNumeric(Left(strTest, Len(strTest) - i)) Then
iResult = Left(strTest, Len(strTest) - i)
Exit For
End If
Next i
MsgBox iResult
End Function

Hth

Rgds
Gunnar Nygaard

 

[Guest]

=Left(strSomeString,Instr(strSomeString,"G")-1)

This assumes it will always be "G"

 

[John Nurick]

Hi Don,

I'm not certain what you mean by a "line of text". If you mean a value
in a text field, you could use something like this as the ControlSource
of a textbox:

=IIf(InStr([X],"G")>0,Left([X],InStr([X],"G")-1),"")

where X is the name of the field. This expression will work as a control
source or in a query, but not in VBA code.

 

[Graham Mandeno]

Hi Don

Just to add to your list of suggestions

The Val function will extract from a text string anything recognisable as a
number, and stop when some non-numeric text is encountered.

So, for example:

Val( "52G 2%"" ) gives 52
Val( "5X 14%" ) gives 5
Val( "5.2B 3%" ) gives 5.2