跳到主要內容

如果單元格包含一個單詞然後將文本放在另一個單元格中，如何處理？

這是產品ID的列表，現在我要查找單元格中是否包含字符串“ KTE”，然後將文本“ TRUE”放入其相鄰的單元格中，如下圖所示。 您有任何快速的方法來解決它嗎？ 在本文中，我將討論以下技巧：查找單元格中是否包含單詞，然後將文本放入相鄰的單元格中。

doc如果單元格包含一個單詞則等於7
如果一個單元格包含一個單詞，則另一個單元格等於特定的文本

如果一個單元格包含一個單詞，則選擇或突出顯示好主意3

如果一個單元格包含一個單詞，則另一個單元格等於特定的文本

這是一個簡單的公式，可以幫助您快速檢查一個單元格是否包含單詞，然後在其下一個單元格中放置文本。

選擇要放置文本的單元格，然後鍵入此公式 = IF（ISNUMBER（SEARCH（“ KTE”，A2）），“ True”，“”） 然後將自動填充手柄向下拖動到要應用此公式的單元格。 看截圖：

doc如果單元格包含一個單詞則等於8 向右射箭 doc如果單元格包含一個單詞則等於9

 

在公式中，A2是要檢查的單元格是否包含特定單詞，而KTE是要檢查的單詞，True是要在另一個單元格中顯示的文本。 您可以根據需要更改這些引用。

如果一個單元格包含一個單詞，則選擇或突出顯示

如果要檢查某個單元格是否包含特定單詞，然後選擇或突出顯示該單詞，則可以應用 選擇特定的單元格 的特點 Excel的Kutools，可以快速處理此工作。

Excel的Kutools, 與以上 300 方便的功能，使您的工作更加輕鬆。 
前往下載
30天免費試用

安裝後 Kutools for Excel，請執行以下操作:(立即免費下載Kutools for Excel！）

1.選擇要檢查單元格是否包含特定單詞的範圍，然後單擊 庫工具 > 選擇 > 選擇特定的單元格。 看截圖：

doc如果單元格包含一個單詞則等於3

2.然後在彈出的對話框中，檢查 細胞 選項，然後選擇 包含 從第一個下拉列表中，然後在下一個文本框中鍵入要檢查的單詞。 看截圖：
doc如果單元格包含一個單詞則等於4

3。 點擊 Ok，會彈出一個對話框，提醒您單元格中可能包含您要查找的單詞，然後單擊 OK 關閉對話框。 看截圖：
doc如果單元格包含一個單詞則等於5

4.然後，選擇包含指定單詞的單元格，如果要突出顯示它們，請轉到 首頁 > 填充顏色 選擇一種填充顏色以突出它們。
doc如果單元格包含一個單詞則等於6

小費。 如果您想免費試用 選擇特定的單元格 功能， 請免費下載Kutools for Excel 首先，然後按照上述步驟進行操作。

最佳辦公生產力工具

🤖 Kutools 人工智慧助手：基於以下內容徹底改變數據分析： 智慧執行   |  生成代碼  |  建立自訂公式  |  分析數據並產生圖表  |  呼叫 Kutools 函數...
熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup  |   多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧   |  工作簿和工作表管理器   |  資源庫 （自動文字）   |  日期選擇器   |  合併工作表   |  加密/解密單元格   |  按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 （過濾粗體/斜體/刪除線...）...
前 15 個工具集:  12 文本 工具 (添加文本, 刪除字符，...）   |   50+ 圖表 類型 (甘特圖，...）   |   40+ 實用 公式 (根據生日計算年齡，...）   |   19 插入 工具 (插入二維碼, 從路徑插入圖片，...）   |   12 轉化 工具 (數字到單詞, 貨幣兌換，...）   |   7 合併與拆分 工具 (高級合併行, 分裂細胞，...）   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能，體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

產品描述

Office選項卡為Office帶來了選項卡式界面，使您的工作更加輕鬆

  • 在Word，Excel，PowerPoint中啟用選項卡式編輯和閱讀，發布者，Access，Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%，每天為您減少數百次鼠標點擊！
Comments (21)
No ratings yet. Be the first to rate!
Flavia
Flavia
This comment was minimized by the moderator on the site
Buenas noches,

Tengo una columna con varias categorías de tareas (administrativo, profesor, etc) y quiero armar la columna siguiente indicando si es x tarea que sea nivel alto, si es otra Medio y así. Intenté con la fórmula SI y SI. CONJUNTO pero no parece funcionar cuando no hay valores numéricos. Vi ejemplos pero con si C2 es mayor que entonces tal palabra...
Reply
0
0
luis alberto
luis alberto
This comment was minimized by the moderator on the site
Buen día, disculpa, queria ver si me puedes ayudar con una formula.

En una celda tengo una oracion, y quiero que la celda de un lado me escriba cierta letra o palabra cuando en la primera celda tenga alguna palabra.

Ejemplo
en la celda A1 tengo una oracion: papa manzana pasta, lechuga.
en la celda A2 tengo: papa manzana, zanahoria, lechuga.

Quiero que en la celda B1 me aparezca la palabra "ensalada" solo porque en la A1 tiene "lechuga"
y en la celda B2 me aparezca la palabra: "arbol" porque tiene la palabra "manzana"

Nota quiero que de prioridad a la lechuga antes que a la manzana
Sino se puede dar prioridad entonces digamos que quitamos la palabra "manzana" de la primera oracion


No se si me di a entender, no se me ocurrio como dar el ejemplo
Reply
Report
0
0
Sunny
Sunny     luis alberto
This comment was minimized by the moderator on the site
Hi, luis, here is a formula may help you.
Take an example for explaining the formula, if cell contains "KTE" then returns "Y", if cell contains "KTW" then returns "T", if cell contains both of "KTE' and "KTW" then returns "Y".
=IF(AND(ISNUMBER(SEARCH("KTE",A3)), ISNUMBER(SEARCH("KTW",A3)) ),"Y", IF(ISNUMBER(SEARCH("KTE",A3)), "Y", IF(ISNUMBER(SEARCH("KTW",A3)), "T", "")))
change the reference and the values as you need.
https://www.extendoffice.com/images/stories/comments/sun-comment/doc-return-value-based-on-word.png
Tip: If you in spanish version, maybe need to change the formula to spanish formula.
Reply
Report
0
0
Andy
Andy
This comment was minimized by the moderator on the site
How do you keep this going, if I have 5 other unique labels that need to spit out 5 other unique corresponding phrases?
Reply
0
0
Sunny
Sunny     Andy
This comment was minimized by the moderator on the site
Hi, Andy, yes, you need to use 5 different formulas to acheive your purpose.
Reply
Report
0
0
andi
andi
This comment was minimized by the moderator on the site
kolom satu
adam
andi
budi
dst

kolom dua
andi jaya
budiarto
adam malik
dst


kolom 3 (hasil pakai rumus)


pertanyaan:
bagaimana rumus di kolom 3, jika kita ingin memunculkan nama kolom 2 dikolom 3 yg mengandung teks di kolom satu.

saya minta petunjuk dari para pakar excel.

Terima kasih
Andi
Reply
0
0
Sunny
Sunny     andi
This comment was minimized by the moderator on the site
Hi, Andi, I realy like to help you, but I cannot read your language, could you login and upload a screenshot to show the result you want? Then I will try to find method to solve it.
Reply
Report
0
0
andi
andi
This comment was minimized by the moderator on the site
kolom satu kolom dua kolom 3 (hasil rumus)
adam andi jaya
andi budiarto
budi adam malik
dst dst

bagaimana rumus di kolom 3, jika kita ingin memunculkan nama kolom 2 dikolom 3 yg mengandung teks di kolom satu.

saya minta petunjuk dari para pakar excel.

Terima kasih
Andi
Reply
0
0
Alex
Alex
This comment was minimized by the moderator on the site
If column has text, alpha-numeric & numeric values, using the convert tool does not apply the text values to numeric. Makes sense. Though, how could you configure a IF Vlookup to compensate if a cell has text?
Reply
0
0
Sunny
Sunny     Alex
This comment was minimized by the moderator on the site
Sorry, which convert tool you mean? The formula can work correctly whatever the column contains alpha, alpha-numeric or numeric string.
Reply
Report
0
0
Sean
Sean
This comment was minimized by the moderator on the site
Hello,

Question. If I need this formula to apply to two different returns. For example, if a cell contains the word "Senior", another cell would return "32" and if the cell contains "Associate" then the other cell would return 16.
Reply
0
0
samgunderson@gmail.com
    Sean
This comment was minimized by the moderator on the site
Here's the formula. Assume there's a list of titles starting in cell A2:

Senior Engineer
Associate Engineer
Senior Engineer
Senior Engineer
Vice President
Associate Engineer
Associate Engineer
Senior Vice Presdent

In cell B2 (or whatever cell you wish to populate with the results), write:

=IF(ISNUMBER(FIND("Senior",A2)),"32",IF(ISNUMBER(FIND("Associate",A2)),"16"))

You could check for additional values by adding additional nested IF statements.
Reply
Report
0
0
Sunny
Sunny     Sean
This comment was minimized by the moderator on the site
Sorry I cannot figore out the correct formula to solve your problem, you can go to our forum https://www.extendoffice.com/forum.html to place your question, maybe someone can help you.
Reply
Report
0
0
Bryanna
Bryanna
This comment was minimized by the moderator on the site
Thanks for this. Can this code be edited so that instead of placing specific text beside the source text, it can be formatted to place the text in a different cell?
Reply
0
0
Helen
Helen     Bryanna
This comment was minimized by the moderator on the site
i know what u mean. i need this function to.
Reply
0
0
Sunny
Sunny     Helen
This comment was minimized by the moderator on the site
Do you mean to extract the text in different cell while the cell contains the specifict text as below screenshot shown?
View Attachments (1 / 5)
Reply
Report
0
0
Sunny
Sunny     Bryanna
This comment was minimized by the moderator on the site
Sorry, I do not realy understand your question. Could you give me more detail description?
Reply
Report
0
0
Kas
Kas
This comment was minimized by the moderator on the site
How would you write the formula is you have text-text-text and you want to pull the final text? AB-GUFJ-CD if you want to pull the CD?
Reply
0
0
Sunny
Sunny     Kas
This comment was minimized by the moderator on the site
Hello, this formula may help you =TRIM(RIGHT(SUBSTITUTE(A12,"-",REPT(" ",255)),255)), A12 is the text string you use, change it as you need. If it works, please let me know. Thanks.
Reply
Report
0
0
Wise Guy
Wise Guy
This comment was minimized by the moderator on the site
LOL ...did you mean True?
Reply
0
0
Sunny
Sunny     Wise Guy
This comment was minimized by the moderator on the site
Embarrrassed...yes, I mean true~~, I have rewrote it, thank u for ur remind.
Reply
Report
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations