Results 1 to 7 of 7

Thread: Finding a word within a text cell

  1. #1
    Acolyte happy_smiler1's Avatar
    Join Date
    Aug 2012
    Posts
    67
    Articles
    0
    Excel Version
    office 365

    Finding a word within a text cell



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Attached Files Attached Files

  2. #2
    Acolyte happy_smiler1's Avatar
    Join Date
    Aug 2012
    Posts
    67
    Articles
    0
    Excel Version
    office 365
    Quote Originally Posted by happy_smiler1 View Post
    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. #3
    Acolyte happy_smiler1's Avatar
    Join Date
    Aug 2012
    Posts
    67
    Articles
    0
    Excel Version
    office 365
    Still having the same issues if anyone can help

    TIA
    Attached Files Attached Files

  4. #4
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,370
    Articles
    0
    Excel Version
    Office 365 Subscription
    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?
    Ali
    Enthusiastic self-taught user of MS Excel!

  5. #5
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,370
    Articles
    0
    Excel Version
    Office 365 Subscription
    Try this for the city:

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

    and this for the county:

    =LOOKUP(2,1/($B2:$G2<>""),$B2:$G2)
    Ali
    Enthusiastic self-taught user of MS Excel!

  6. #6
    Acolyte happy_smiler1's Avatar
    Join Date
    Aug 2012
    Posts
    67
    Articles
    0
    Excel Version
    office 365
    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. #7
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,370
    Articles
    0
    Excel Version
    Office 365 Subscription
    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.
    Ali
    Enthusiastic self-taught user of MS Excel!

Posting Permissions

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