locating data in the middle of one field

[balllian]

is it possible to locate the following wording in one field of text. TotalnoRooms='2'

there is a variance in the last part of this where the last part could read ='6' or ='165'

The workding is in the middle of lots of data within one field.

Thanks in advance

Ian

 

[lespaul]

sounds like mid() is what you are looking for.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[PHV]

You may also have to play with the InStr and/or InStrRev functions.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TonyJollans]

.. or use [blue]WHERE Field Like "*TotalnoRooms='2'*"[/blue] in a Query.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[balllian]

There is a slight variance in each record in that each row of data contains different data so the last part will be different for each row. One row may say '2' and another will say '5'

 

[TonyJollans]

And .. do you want to retrieve records with the '2' .. or with the '5' .. or with both .. or with anything .. or what?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[PHV]

You may try this:
Left(Mid([FieldOfText],InStr(1,[FieldOfText],"TotalnoRooms='",1)), InStr(15,Mid([FieldOfText],InStr(1,[FieldOfText],"TotalnoRooms='",1)),"'"))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RoyVidar]

Or pass it to a public function, and use Regular Expressions?

[tt]Private gre As Object

Public Function GetString(ByVal v_strInString As String) As String
Dim mc As Object
If (gre Is Nothing) Then
Set gre = CreateObject("VBScript.RegExp")
End If
gre.Pattern = "TotalnoRooms='\d+'"
Set mc = gre.Execute(v_strInString)
If (mc.Count > 0) Then
GetString = mc(0)
End If
Set mc = Nothing
End Function[/tt]

This should return zls if not found, or the whole string if found (TotalnoRooms='<some numeric>'), and #Error if the field is Null (prevent by declaring the parameter as variant in stead of string).

But it would perhaps be better to ensure quality when retrieving the data? Normalization, validation...?

Roy-Vidar

 

[OhioSteve]

In sql, asterisks mean "any or none" so the criterion

like "*bo*"

finds "about", "boat", and "bo