Short Answer
Instead of LEFT() use RIGHT().
Explanation
The LEFT() function returns the leftmost characters, but the OP want to get the rightmost characters.
The following is a simplified version of the final formula, for explanatory purposes:
=RIGHT("Sector: Industrial Goods",16)
The following is the final formula; in order to improve readability, each parameter is inserted in a new line:
=RIGHT(
iferror(
INDEX(
IMPORTHTML(
"http://www.thestreet.com/quote/GE/details/company-profile.html",
"table",
3
),
8,
1
)
),
16
)
The easiest way to do this is with a regexreplace function, it effectively also preserves strings as strings by default, and its quite dynamic, making it one of my favorite functions:
=IFERROR(REGEXEXTRACT(A4,"("®EXREPLACE(A4,", ","), (")&")"),A4)
To Explain:
I start by building by own regex dynamically by placing the ,
(comma with the space) between to opposite facing parentheses, which partially create capture groups around the pieces of text I do want.
which starts to create a string that looks like this:
Thursday 8-9), (Thursday 9-10), (Thursday 7-8
To complete the capture groups, I start and end my string with the opening and closing parentheses like this:
="("®EXREPLACE(A4,", ","), (")&")")
which effectively would create a variable like this:
(Thursday 8-9), (Thursday 9-10), (Thursday 7-8)
By default a capture group automatically pushes each group to it's own cell, and since im not telling it what has to be in the parentheses, only that it should use the comma and space to know where the groups end and begin, i top it off with a regex extract function.
For the cases where there are no commas to replace, it would throw an error, so the easiest way is to wrap the whole thing with iferror
so that it simply returns the original cell in those cases:
![enter image description here](https://i.stack.imgur.com/LEVsd.png)
Best Answer
To get the time out of timestamps found in mixed text, try this formula
(where
A2
is the cell with our text):Functions used:
Mention: I noticed that you also posted your question on stackoverflow as well where you also got another variation of a working solution.
So to use the best of both worlds (shorter, returns a number a well instead of a text), you can try this: