Извлечь целое слово из строки, содержащей определенную букву или символ

2207
user498469

Я хотел бы извлечь только слово из ячейки, которая содержит определенный символ ("=") в тексте. 

A2: Dolly made me a homemade=cake and some muffins A3: we had cheese=cake for dinner A4: Everyone loves how the bakery makes some awesome=cakes

Я хотел бы, чтобы следующее в столбце (A2: A4) предоставило следующие результаты в столбце (B2: B4).

B2: homemade=cake B3: cheese=cake B4: awesome=cakes

Я пробовал следующее решение с функциями листа.

A2: Johnny made his own dinner=lastnight and then cleaned the kitchen  =TRIM(TRIM(IFERROR(RIGHT(SUBSTITUTE(LEFT(A2,SEARCH(" ",A2&" ",SEARCH("=",A2))-1)," ",REPT(" ",LEN(A2))),LEN(C246)),""))),""),

Эта формула не работает, как вы можете видеть, поскольку она извлекает данные только справа от символа ("=") со следующими результатами. Принимая во внимание, что я ищу решение, которое извлечет целое слово (справа и слева) из символа ("=").

3
`C3:` или B4: `? Ваш вопрос противоречив. DavidPostill 8 лет назад 0
`sed -r 's /.* \ b ([^ \ t] + = [^ \ t] +) \ b. * / \ 1 /'` .. извините, не смог устоять. Peter Schneider 8 лет назад 2

2 ответа на вопрос

1
Gary's Student

Try the following User Defined Function (UDF)

Public Function Equals(inpt As String) As String Equals = "" ary = Split(Application.WorksheetFunction.Trim(inpt), " ") For Each a In ary If InStr(1, a, "=") > 0 Then Equals = a Exit Function End If Next a End Function

enter image description here

User Defined Functions (UDFs) are very easy to install and use:

  1. ALT-F11 brings up the VBE window
  2. ALT-I ALT-M opens a fresh module
  3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

  1. bring up the VBE window as above
  2. clear the code out
  3. close the VBE window

To use the UDF from Excel:

=equals(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this to work!

EDIT#1:

You can do this without VBA, using the same methodology. With data in A1, in C1 enter:

=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",999)),COLUMNS($A:A)*999-998,999))

and copy across. Then in B1 enter:

=INDEX(C1:IV1,MATCH("*=*",C1:IV1,0))

enter image description here

-1
Bekzod Buranov

Excel has a number of text functions that can help you to extract a word or a text from another text string. Which function, or combination of functions, to use depends on your situation. Example: Suppose that cell A1 has the text string: “Creativity requires the courage to let go of certainties”

1. To get the first 5 characters of the string, we use the LEFT function: =LEFT(A1,5) The result is “Creat”

2. To get the last 11 characters of the string, we use the RIGHT function: =RIGHT(A1,11) The result is “certainties”

3. To get 7 characters from the string starting with position 10, we use the MID function: =MID(A1,10,7) The result is “y requi”.

4.To get the first word of the string, we use the LEFT and FIND functions: =LEFT(A1,(FIND(” “,A1)-1)) The result is “Creativity”.

- See more at: http://www.exceldigest.com/myblog/2009/02/01/how-to-extract-text-from-another-text-string/#sthash.yNDHU7Xe.dpuf

Похожие вопросы