Finding a word within a text cell

H

happy_smiler1

New member
Joined
Aug 28, 2012
Messages
67
Reaction score
0
Points
0
Excel Version(s)
office 365
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.
 

Attachments

  • test.xlsx
    10.2 KB · Views: 13
happy_smiler1 said:
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.
Click to expand...

I found an answer to the question by using the following formula:

=TRIM(LEFT(RIGHT(" "&SUBSTITUTE(TRIM(A2),", ",REPT(" ",60)),120),60))
 
Still having the same issues if anyone can help

TIA
 

Attachments

  • test.xlsx
    10.9 KB · Views: 10
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?
 
Try this for the city:

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

and this for the county:

=LOOKUP(2,1/($B2:$G2<>""),$B2:$G2)
 
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?
 
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.
 
You must log in or register to reply here.
Back
Top