+ Reply to Thread
Results 1 to 14 of 14

Need to do something with a cell that contain specific text

  1. #1
    Registered User
    Join Date
    12-07-2017
    Location
    Los Angeles, California
    MS-Off Ver
    2013
    Posts
    12

    Need to do something with a cell that contain specific text

    If cell A1 contains a certain substring in it, I'd like to return a value in cell B1. For instance, if cell A1 contains a city such as "Los Angeles" in it, I would like to return its region in cell B1, which is "West." Or, if that same cell A1 contains another city, such as "Boston," I'd like cell B1 to return "East." Does anybody have a formula to achieve this?

    Thanks.

  2. #2
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Need to do something with a cell that contain specific text

    Do you have list of cities and their region in excel? If you have then you can simply use Vlookup function. Have a look at below link for better and clean information.

    https://exceljet.net/excel-functions...ookup-function






    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Need to do something with a cell that contain specific text

    You will need a table to allocate city to region. then a combination of "Search" to find City and "Lookup" to allocate to region.

    Attach a sample workbook (not image).

    Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

    Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  4. #4
    Registered User
    Join Date
    12-07-2017
    Location
    Los Angeles, California
    MS-Off Ver
    2013
    Posts
    12

    Re: Need to do something with a cell that contain specific text

    First timer here. Thank you both! Before finding this site, I would attempt to Google the formulas I needed for hours on end. I will work on allocating the city to the region in a chart and up upload if need be.

  5. #5
    Registered User
    Join Date
    12-07-2017
    Location
    Los Angeles, California
    MS-Off Ver
    2013
    Posts
    12

    Re: Need to do something with a cell that contain specific text

    Please see the attachment as an example of what I mean.
    Attached Files Attached Files

  6. #6
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Need to do something with a cell that contain specific text

    Try

    B2
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Note in Miami Lookup table there is additional space.

    Copy down.
    Last edited by shukla.ankur281190; 12-08-2017 at 03:59 AM. Reason: Note updated

  7. #7
    Registered User
    Join Date
    12-07-2017
    Location
    Los Angeles, California
    MS-Off Ver
    2013
    Posts
    12

    Re: Need to do something with a cell that contain specific text

    Thank you. That works for most of them. However, cell B5 is showing #N/A?

  8. #8
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Need to do something with a cell that contain specific text

    There is additional space mention in Miami in VlookupTable, remove that additional space see it will work

  9. #9
    Registered User
    Join Date
    12-07-2017
    Location
    Los Angeles, California
    MS-Off Ver
    2013
    Posts
    12

    Re: Need to do something with a cell that contain specific text

    Thank you so much! That fixed it!

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Need to do something with a cell that contain specific text

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  11. #11
    Registered User
    Join Date
    12-07-2017
    Location
    Los Angeles, California
    MS-Off Ver
    2013
    Posts
    12

    Re: Need to do something with a cell that contain specific text

    Yep did that - thanks John.

  12. #12
    Registered User
    Join Date
    12-07-2017
    Location
    Los Angeles, California
    MS-Off Ver
    2013
    Posts
    12

    Re: Need to do something with a cell that contain specific text

    Hi Guys,
    How would I incorporate the same formula above to drive off a different tab, such as the attachment. Want to use the same function, but just separate the information on two different tabs. Please see attachment. Thanks.
    Attached Files Attached Files

  13. #13
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Need to do something with a cell that contain specific text

    Try

    =LOOKUP(99*99,SEARCH(BEFORE!$L$2:$M$8,BEFORE!$A2),BEFORE!$M$2:$M$8)

  14. #14
    Registered User
    Join Date
    12-07-2017
    Location
    Los Angeles, California
    MS-Off Ver
    2013
    Posts
    12

    Re: Need to do something with a cell that contain specific text

    That worked - thanks a bunch John!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] If a cell contains text insert specific text in specific cells in row
    By muss1210 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-18-2017, 10:51 AM
  2. [SOLVED] Enter data into a userform text box to show in a specific cell in a specific worksheet
    By Stevecraig211 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-10-2016, 10:02 AM
  3. [SOLVED] VBA to Find Specific Text, then Copy range to next open cell under specific text
    By Remotruker in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-24-2015, 10:59 AM
  4. Replies: 2
    Last Post: 08-05-2013, 07:32 PM
  5. Replies: 3
    Last Post: 07-12-2013, 09:17 AM
  6. [SOLVED] Formula with specific text that inserts value from BESIDE cell with specific text.
    By jtmann in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-13-2012, 09:24 AM
  7. Replies: 1
    Last Post: 06-22-2012, 02:18 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1