Hi, I have a spreadsheet whereby there is column A and I have created a macro to insert column B, insert the following function and drag it down:
=LEFT(A4,SEARCH("Cat",A4)-1)
This way, if in column A I have a sentence such as "Black cat" the only thing that remains is "Black". And this function is in B1:B20
However some of the values from A1:A20 are written such as "22-01-13 Monday" so as they change every week, the cells are not formatted as Date, and there is no consistent space in between the cells that hold the two or more dates. These values give the error #VALUE!, however, I want these cells to display the dates that are in column A, rather than the error.
To no avail I tried the following function, placed in B5:
=IF((A5,SEARCH("Cat",A5), (LEFT(A5,SEARCH("Cat",A5)-1), A5)))
And this is what I'm trying to tell excel to do with it: if you find "Cat" in A5, leave any of the words left of "Cat", if you don't find "Cat" make B5 = A5
Also, I know that there is an ISERROR function however I do not know how to use it for what I want as there is already a function in the cells.
How would I got about this? I'm also open to creating a macro
Bookmarks