Extract information before several special characters in excel

user_12302990 0
Viewed 38

I have a problem with an excel file.

I am trying to extract information from a column. This information appears randomly, before a ".", "-" or ":". So an example would be:

CELL EXPECTED RESULT
hi.this is: hi
maybe I- this works maybe I
Who is: what. like- Who is

I am using the formula:

=MID(A1,1,FIND("-",A1,1)-1)

Using this one, I get the information I need, but I am not able to add the other characters (".", ":",...) to the formula. Also I have the problem that in a same cell, I can have several of this characters, and I only want the information before the FIRST character (of all posible kinds) that appears in the cell.

I dont know if somebody can help me here.

Thank you very much in advance!

ms-office excel-formula excel
1 Answers

You can try:

enter image description here

Formula in B1:

=TEXTBEFORE(A1:A3,{".","-",":"})

If you don't yet have acces to TEXTBEFORE() then try:

=LEFT(A1,MIN(FIND({".","-",":"},A1&".-:"))-1)

I suppose this is an array-entered formula in versions prior to ms365.

user_97581940
Related