# Thread: Finding a word within a text cell

1. ## Finding a word within a text cell

Hi All,

Kind of stuck with trying to find a word within a text cell. I have attached a workbook with some examples. In column "I", I need to find the city from the text field in column H, however it is the text in the middle so for example Ipswich, Suffolk I would need it to pull out Ipswich only. I have tried mid/len/substitute, however none of these give me what I need, if anyone could help.

Would really be appreciated.

2. Originally Posted by happy_smiler1
Hi All,

Kind of stuck with trying to find a word within a text cell. I have attached a workbook with some examples. In column "I", I need to find the city from the text field in column H, however it is the text in the middle so for example Ipswich, Suffolk I would need it to pull out Ipswich only. I have tried mid/len/substitute, however none of these give me what I need, if anyone could help.

Would really be appreciated.
I found an answer to the question by using the following formula:

=TRIM(LEFT(RIGHT(" "&SUBSTITUTE(TRIM(A2),", ",REPT(" ",60)),120),60))

3. Still having the same issues if anyone can help

TIA

4. Why are you trying to do this from the concatenated cell when you can do it using the columns to the left??? What's the objective here?

5. Try this for the city:

=LOOKUP(2,1/(\$B2:\$G2<>""),\$A2:\$F2)

and this for the county:

=LOOKUP(2,1/(\$B2:\$G2<>""),\$B2:\$G2)

6. Thank you, it worked a treat. There was no reason, I just thought it would be easier to concatenate the fields and then using the trim/left/right and substitute function to get what I needed, but yours was way simpler. Are you able to tell me what its doing for my learning curve?

7. Yes. The first half looks for the last cell in the range that is not blank. In the second formula it then returns the value in that cell (second range in the formula). In the first formula, it returns the column before (the second range is offset by one from the first).

Hope this helps.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•