Need formula in query to return part of string after blank space

J

Joe M.

I have a query that needs to return the part of the original string after the
first blank. In other words I want to cut off the first word as shown below
in Short_desc. Originally I was using the following formula when I thought
the first word was "The". In this formula, if "The" is not present then the
result is the original string.

Short_Desc: IIf(Left([Field1],3)="The",Mid([Field1],5,35),[Field1])

Here's what I need. Note that if there is only one word then the entire
string is returned (record 4).

Field1 Short_Desc (Desired result)
Record 1: "The Apples" "Apples"
Record 2: "Another Time" "Time"
Record 3: "What problem" "Problem"
Record 4: "Alltogether" "Alltogther"

Can someone help?
Thanks,
Joe M.
 
K

kc-mass

Try this:

Short_Desc: IIF(instr(Field1, " ")
0,IIf(Left([Field1],3)="The",Mid([Field1],instr(Field1,"
Click to expand...
")+1,35),[Field1]))

Regards

Kevin


Joe M. said:
I have a query that needs to return the part of the original string after
the
first blank. In other words I want to cut off the first word as shown
below
in Short_desc. Originally I was using the following formula when I thought
the first word was "The". In this formula, if "The" is not present then
the
result is the original string.

Short_Desc: IIf(Left([Field1],3)="The",Mid([Field1],5,35),[Field1])

Here's what I need. Note that if there is only one word then the entire
string is returned (record 4).

Field1 Short_Desc (Desired result)
Record 1: "The Apples" "Apples"
Record 2: "Another Time" "Time"
Record 3: "What problem" "Problem"
Record 4: "Alltogether" "Alltogther"

Can someone help?
Thanks,
Joe M.
Click to expand...
 
A

Al Campagna

kc-mass,
The OP wasn't very clear as regards "The."
I went by the "examples given" and the "desired results"...
Your calculation looks for a "The" in each string.

I think the OP meant that if there are ANY two words, separated by a
space...
return the second word. If no space... return the whole string.

Al Campagna

kc-mass said:
Try this:

Short_Desc: IIF(instr(Field1, " ")
0,IIf(Left([Field1],3)="The",Mid([Field1],instr(Field1,"
Click to expand...
")+1,35),[Field1]))

Regards

Kevin


Joe M. said:
I have a query that needs to return the part of the original string after
the
first blank. In other words I want to cut off the first word as shown
below
in Short_desc. Originally I was using the following formula when I
thought
the first word was "The". In this formula, if "The" is not present then
the
result is the original string.

Short_Desc: IIf(Left([Field1],3)="The",Mid([Field1],5,35),[Field1])

Here's what I need. Note that if there is only one word then the entire
string is returned (record 4).

Field1 Short_Desc (Desired result)
Record 1: "The Apples" "Apples"
Record 2: "Another Time" "Time"
Record 3: "What problem" "Problem"
Record 4: "Alltogether" "Alltogther"

Can someone help?
Thanks,
Joe M.
Click to expand...
Click to expand...
 
J

Joe M.

Hi. I will try that. In the meantime, I found another solution that works:

Short_Desc2: Right([Field1],Len([Field1])-InStr([Field1]," "))

stumac said:
Hi Joe, try:

Short_Desc: Mid([field1],InStr(1,[field1]," ")+1)

Hth

Stu

Joe M. said:
I have a query that needs to return the part of the original string after the
first blank. In other words I want to cut off the first word as shown below
in Short_desc. Originally I was using the following formula when I thought
the first word was "The". In this formula, if "The" is not present then the
result is the original string.

Short_Desc: IIf(Left([Field1],3)="The",Mid([Field1],5,35),[Field1])

Here's what I need. Note that if there is only one word then the entire
string is returned (record 4).

Field1 Short_Desc (Desired result)
Record 1: "The Apples" "Apples"
Record 2: "Another Time" "Time"
Record 3: "What problem" "Problem"
Record 4: "Alltogether" "Alltogther"

Can someone help?
Thanks,
Joe M.
Click to expand...
Click to expand...
 
K

kc-mass

Sorry. I copied the original and did not complete the editing

Regards
Al Campagna said:
kc-mass,
The OP wasn't very clear as regards "The."
I went by the "examples given" and the "desired results"...
Your calculation looks for a "The" in each string.

I think the OP meant that if there are ANY two words, separated by a
space...
return the second word. If no space... return the whole string.

Al Campagna

kc-mass said:
Try this:

Short_Desc: IIF(instr(Field1, " ")
0,IIf(Left([Field1],3)="The",Mid([Field1],instr(Field1,"
Click to expand...
")+1,35),[Field1]))

Regards

Kevin


Joe M. said:
I have a query that needs to return the part of the original string after
the
first blank. In other words I want to cut off the first word as shown
below
in Short_desc. Originally I was using the following formula when I
thought
the first word was "The". In this formula, if "The" is not present then
the
result is the original string.

Short_Desc: IIf(Left([Field1],3)="The",Mid([Field1],5,35),[Field1])

Here's what I need. Note that if there is only one word then the entire
string is returned (record 4).

Field1 Short_Desc (Desired result)
Record 1: "The Apples" "Apples"
Record 2: "Another Time" "Time"
Record 3: "What problem" "Problem"
Record 4: "Alltogether" "Alltogther"

Can someone help?
Thanks,
Joe M.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I return a blank in a IF formula? 2
Excel Drag autofill only part of formula 3
Sum Fields which have blank records 2
Query Criteria IIf statement 4
Cell reference in formula string displays abbreviated value 2
Query to eliminate blank fields 4
Concatenate in query with line feeds 3
Make Table Query Only Runs After Edit 1

Top