Expression to find a number in a string in a field and combine it with a string as the result

[martyk]

Is it possible to build an expression to look for the first number in a string it comes across in a particular field then combine it with a small string to make the result.
The value of the field in question will only Contain "Action N" or "Task Number N" where N is number from 1 to 9. The result in Expression field would be "Confirmed N".

[CJ_London]

use the mid and instrrev functions - in the immediate window

?val(mid("Task Number 123",instrrev("Task Number 123"," ")))
123


?val(mid("Action 456",instrrev("Action 456"," ")))
456

I used Val to convert the text type of 123 to a number, you may or may not need it depending on what you are doing with the returned value - in this case you don't need it

google 'vba mid function' and 'vba instrrev function' to find out more about these function

For your specific requirement you could use the replace function instead


?replace(replace("Task Number 123","Task Number", "Confirmed"),"Action", "Confirmed")
Confirmed 123


?replace(replace("Action 123","Task Number", "Confirmed"),"Action", "Confirmed")
Confirmed 123

[Micron]

Perhaps the actual words might need to be replaced with field references.

[martyk]

I'm not sure i could do this using VBA as its kind of locked down where I work .I think its better that it is done by a query expression in a new field /column (next to the field with the string in question) that would hold the outcome.
e.g check for a number in the first 14 or so characters of the string, return the number and concatonate it with the word "confirmed" and put it in the column where the query expression is held. e.g. if the column/field the expression is looking at contains "Action 5" or "Task number 6" it will return "5" or "6" and puts the string "Confirmed 5" or "Confirmed 6" in the field within the column where the query expression lives.

use the mid and instrrev functions - in the immediate window

?val(mid("Task Number 123",instrrev("Task Number 123"," ")))
123


?val(mid("Action 456",instrrev("Action 456"," ")))
456

I used Val to convert the text type of 123 to a number, you may or may not need it depending on what you are doing with the returned value - in this case you don't need it

google 'vba mid function' and 'vba instrrev function' to find out more about these function

For your specific requirement you could use the replace function instead


?replace(replace("Task Number 123","Task Number", "Confirmed"),"Action", "Confirmed")
Confirmed 123


?replace(replace("Action 123","Task Number", "Confirmed"),"Action", "Confirmed")
Confirmed 123

[CJ_London]

So you haven't bothered to try my example in a query - So here is an example in a query

SELECT myField, replace(replace([myField],"Task Number", "Confirmed"),"Action", "Confirmed") as Confirmed
FROM myTable

e.g check for a number in the first 14 or so characters of the string

So now you are changing the requirement to a vaguer one.

I'm not prepared to waste my time suggesting solutions when the requirement is not clear. So you won't hear from me again until you have a clear requirement, preferably with examples.

[Micron]

You can use those functions in expressions in query fields if that's what you need to do. You don't have to use them in code.

[martyk]

Actually I have tried (mid("Task Number 123",instrrev("Task Number 123"," "))) in the Query expression and it works fine. My point is I need pull out these numbers out using the one query which needs to deal with two strings of different length within this data set. Hence my attempt to clarify my requirements and provide more context.
So for a table with 100 or more rows for a specific column, the String in each row is proceeded with either Task Number 123 .... or Action 123 ..the remainder of the string is just descriptive text. Creating a list of these numbers in a new column is the requirement in this context.
If I can get this to work I use it in other table with different string lengths.

[Micron]

If you're stuck, copy/paste from the query into Excel then copy/post from Excel. Then edit the xl sheet to reflect what you want and post that. Then we'll have the input and the desired results.

[CJ_London]

In that case use a combination of the suggestions in post #2

?"Confirmed " & val(replace(replace("Task Number 123 some text","Task Number", ""),"Action", ""))
Confirmed 123


?"Confirmed " & val(replace(replace("Action 45 more verbiage","Task Number", ""),"Action", ""))
Confirmed 45

[martyk]

I built this code and it seems to work perfectly in EXCEL ="Confirmed "&MID(F2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},F2&"012345 6789")),3)

Consolidated	Tracking Event
Confirmed 123	Action 123 : The purpose of this action is to ensure there is no immediate effect on the Charge points in the event of a brown out for up to 8 hours
Confirmed 121	Task Number 121
Confirmed 11	Task number 11
Confirmed 20	Task number 20
Confirmed 24	Action 24
Confirmed 23	Observation 23
Confirmed 15	Note Number 15

I then plugged the expression into Access and I get a GUID error ..."The expression contains a Malformed Guid Constant"

My Access VBA skills are pretty rusty I have not had the need to look into this stuff for over 10 years ..can help me out here?