How to find first word on the last line in a string

[HelpSeeker]

Good 'moro!

I'm working on a project in which I have become stumped. I'm here in hope that what I'm trying to do is in fact possible. I'm trying to use a formula to search a cell in such a way that it will pick out the first word on the last line. Specifically, it will be the time stamp in the format of XX:XX:XX. I'll give you an understanding of how that cell will look:


[Time] [Name A]: [Text]
[Time] [Name B]: [Text]
[Time] [Name A]: [Text]
[Time] [Name B]: [Text]


I have been experimenting with some code but I have been unsuccessful with achieving the desired results. I've used

=LEFT(A2,FIND(" ",A2&" "))

which is perfect for selecting the time from the first line. I have tried some other functions such as RIGHT, ISERR, TRIM and SUBSTITUTE without luck on the last line too.

Is this an achievable goal? Just to make matters more complicated, I would also need to find the time from the second-to-last line separately too. One step at a time though, right?

Thanks in advance for any advice!

 

[FormR]

Hi, welcome to the forum!

So all of the below is in one cell? Separated by carriage returns?

[Time] [Name A]: [Text]
[Time] [Name B]: [Text]
[Time] [Name A]: [Text]
[Time] [Name B]: [Text]

If so maybe..

Excel 2013/2016
	A	B
1	[Time] [Name A]: [Text] [Time] [Name B]: [Text] [Time] [Name A]: [Text] 05:10:23 [Name B]: [Text]	05:10:23
Sheet1

Cell Formulas
Range	Formula
B1	=0+LEFT(TRIM(RIGHT(SUBSTITUTE(A1,CHAR(10),REPT(" ",255)),255)),8)

 

[HelpSeeker]

Thank you for the welcome!

Ah, right. I should have specified. Yes, all the data is in a single cell and each line appears to be separated by a carriage return (the data is pulled from different software). Initially, the data appears as a single line until Text Wrap is selected. That said, I have some odd results when attempting to use CHAR(10) in the formula. For example;

This formula locates the last line but only returns the latter half of it where there is no carriage return at all.

=RIGHT(A2,FIND(CHAR(10),A2&" "))

I've tried using your formula (thank you!) but unfortunately it returns #VALUE !

 

[FormR]

I've tried using your formula (thank you!) but unfortunately it returns #VALUE !

Hi, I'm not around for much longer today - but I think two things will help to diagnose.

1. Can you post a couple of representative sets of example data.
2. For each of those examples can you tell us what the formula in post#2 returns when you remove the '0+' from the start if it.

 

[HelpSeeker]

Thank you for your time and replies!

Absolutely, I can provide you with some representative data but I can not use the real one due to data protection. I'll attempt to make it realistic and possible and include some characters that may apply within the data.

14:47:20 John: In varius vehicula justo, nec congue neque gravida eu. Fusce placerat nisl felis, vel lobortis diam £20 aliquet ac. Proin laoreet varius lacus quis interdum. Aliquam quis lorem lacus. Aenean elementum orci vitae erat aliquet elementum
14:58:48 Jane: Praesent accumsan euismod enim, sit "amet commodo orci" commodo id. Nunc interdum viverra euismod
15:00:31 John: Nam arcu enim, molestie quis risus a, eleifend dapibus magna. Nullam at urna dolor
15:01:24 Jane: Lorem ipsum dolor sit amet, consectetur adipiscing elit. Vivamus vel laoreet sem, suspendisse potenti, sed convallis maximus '+' tincidunt aenean sed commodo risus 'Proin lobortis' gravida posuere.

The following is how the data will appear in the cell after a report it pulled from the other software before applying Text Wrap:

14:47:20 John: In varius vehicula justo, nec congue neque gravida eu. Fusce placerat nisl felis, vel lobortis diam £20 aliquet ac. Proin laoreet varius lacus quis interdum. Aliquam quis lorem lacus. Aenean elementum orci vitae erat aliquet elementum14:58:48 Jane: Praesent accumsan euismod enim, sit "amet commodo orci" commodo id. Nunc interdum viverra euismod15:00:31 John: Nam arcu enim, molestie quis risus a, eleifend dapibus magna. Nullam at urna dolor15:01:24 Jane: Lorem ipsum dolor sit amet, consectetur adipiscing elit. Vivamus vel laoreet sem, suspendisse potenti, sed convallis maximus '+' tincidunt aenean sed commodo risus 'Proin lobortis' gravida posuere.

The cell without '0+' no longer provides #VALUE! but the cell is empty.

 

[FormR]

Hi, your text is quite long so this alternative (although it's unlikely to solve the problem) will likely prove more robust.

=LEFT(TRIM(RIGHT(SUBSTITUTE(A1,CHAR(10),REPT(" ",LEN(A1))),LEN(A1))),8)

The cell without '0+' no longer provides #VALUE ! but the cell is empty.

I wonder if you have an errant carriage return on the end of your data. This might help to highlight where your CR's are and enable you to see if they are where you expect them to be.

=SUBSTITUTE(A1,CHAR(10),"##############")

Can you post the result of this formula for your first set of example data?

 

[HelpSeeker]

Hello, thanks again for replying/helping!

Thank you for the LEN formula. This will definitely be more useful. I'm not aware of a character limit for each line so it could potentially get messy.

I wonder if you have an errant carriage return on the end of your data. This might help to highlight where your CR's are and enable you to see if they are where you expect them to be.

=SUBSTITUTE(A1,CHAR(10),"##############")

I've tried this one. The results are as follows (colour added for ease of reading):

[14:47:20 John: In varius vehicula justo, nec congue neque gravida eu. Fusce placerat nisl felis, vel lobortis diam £20 aliquet ac. Proin laoreet varius lacus quis interdum. Aliquam quis lorem lacus. Aenean elementum orci vitae erat aliquet elementum##############14:58:48 Jane: Praesent accumsan euismod enim, sit "amet commodo orci" commodo id. Nunc interdum viverra euismod##############15:00:31 John: Nam arcu enim, molestie quis risus a, eleifend dapibus magna. Nullam at urna dolor##############15:01:24 Jane: Lorem ipsum dolor sit amet, consectetur adipiscing elit. Vivamus vel laoreet sem, suspendisse potenti, sed convallis maximus '+' tincidunt aenean sed commodo risus 'Proin lobortis' gravida posuere.##############

I'm guessing the last carriage return is the cause of the problem currently.

 

[FormR]

I'm guessing the last carriage return is the cause of the problem currently.

Hi, yep - the question now becomes, is the last CR always there?

If it is:

=LEFT(TRIM(RIGHT(SUBSTITUTE(LEFT(A1,LEN(A1)-1),CHAR(10),REPT(" ",LEN(A1))),LEN(A1))),8)

If this works, then try adding back to "0+" to convert the text string to a proper Excel time serial number (you may need to format the cell containing the formula as time)

 

[HelpSeeker]

Hi, yep - the question now becomes, is the last CR always there?

If it is:

=LEFT(TRIM(RIGHT(SUBSTITUTE(LEFT(A1,LEN(A1)-1),CHAR(10),REPT(" ",LEN(A1))),LEN(A1))),8)

If this works, then try adding back to "0+" to convert the text string to a proper Excel time serial number (you may need to format the cell containing the formula as time)

Hello, thanks again for the speedy reply!

I'll need to pull some more data for varying instances to be sure but I'm 99.9% certain the last carriage return will always be there.

I've tried the above formula without success. It's presenting the #VALUE ! result again

 

[FormR]

I've tried the above formula without success. It's presenting the [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=VALUE]#VALUE [/URL] ! result again

Hi, it's actually quite difficult for the formula in post 8 to return #VALUE ! where the previous suggestion without the 0+ returned a blank.

Are you sure that you correctly updated all the cell references to your actual cell when implementing?