1

Lets say I have one cell that contains the following text:

"Blah blah blah blah blah. Bleh: word word word word." 

Is there a function to cut/paste everything from Bleh: onward into another cell (based on the text Bleh- not string length)?

I've tried tinkering and googling etc but have not found much so far. Thank you in advance for any advice/help.

1
  • FYI, I've added a second formula to my answer.
    – robinCTS
    Jun 9, 2018 at 9:42

3 Answers 3

1

Assuming your text is in A1

=IFERROR(IF(FIND("bleh",A1)>0,RIGHT(A1,LEN(A1)+1 - FIND("bleh",A1)),""),"")
0
5

What you are actually after is a MID() function that optionally allows two arguments, resulting in returning the text from the position of the supplied second argument, through to the end of the string.

Unfortunately, Excel's MID() function doesn't allow for that. So a substitute formula needs to be crafted.


Assuming the text is in A1, the simplest formula is:

=IFERROR(MID(A1,FIND("Bleh:",A1),LEN(A1)),"")


The other way of creating a substitute formula is by using RIGHT(), like cybernetic.nomad did in this answer. However, the best way to do so is the following:

=IFERROR(RIGHT(A1,LEN(A1)+1-FIND("Bleh:",A1)),"")


Whilst, technically speaking the second formula is the more "accurate" one (it calculates and returns precisely the correct number of rightmost characters, as opposed to the first formula that tries to grab more characters than what actually exist), the first formula contains two less operaters (yeah, faster!) and is shorter (gotta save on those expensive harddisk/SD bytes ;))

-2

You can use this also.

=MID(A2,FIND(".",A2)+2,256)

You get, Bleh: word word word word

Other is, the most easiest alternative, Text to Column Command.

  • Select Delimited while 1st step.
  • During step 2 select Other and put "Full Stop" sign in text box next to it.
  • Finally click either Next or Finish.

You get, Bleh: word word word word

6
  • 1
    "Bleh:" is the reliable target specified in the question. Everything that comes before it is nonsense filler. You can't rely on a period being in that position.
    – fixer1234
    Jun 9, 2018 at 8:30
  • #Fixer1234 Yeah ,, solution using Bleh is already been posted by #robinCTC so I thought better use different one. Jun 9, 2018 at 8:38
  • 1
    Rajesh - I agree with @fixer1234. This is basically a useless answer. Plus your formula also doesn't work if there are more than 256 characters from the search string onwards. (A cell can contain up to 32767 characters.) You really shouldn't be posting answers essentially the same as existing ones unless you are contributing something extra that is useful. You especially shouldn't be doing so if your answer is essentially the same, but modified just enough so that it is completely broken. You would have been better posting =MID(A2,27,22) as it is equally useless, but at least correct ;-)
    – robinCTS
    Jun 9, 2018 at 9:06
  • @robinCTS, I've not noticed answers below, including yours, and I don't believe in stealing text or anything, was a coincident only. For few problems, "to solve method is similar" so that sometimes even the FORMULA resembles. Jun 10, 2018 at 11:54
  • You should read all the other answers before posting your own. That way you'll see if your answer is the same as someone else's, And yes, sometimes the solutions come out the same. Which is why, I stress again, always read the other answers before posting your own. It may have been a coincidence, but you clearly stated in a previous comment ("#Fixer1234 Yeah ,, solution using Bleh is already been posted by #robinCTC so I thought better use different one") that you had read my answer.
    – robinCTS
    Jun 10, 2018 at 12:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.