Formula to find characters after the last space in a string

[DougStroud]

I have text string and would like two formulas to find, 1) the characters after the last space and 2)to find the number of characters after the last space.

thanks

ds

 

[blazonQC]

I have text string and would like two formulas to find, 1) the characters after the last space and 2)to find the number of characters after the last space.

This question ^ is just asking for the last part of the string and the length of the returned string. - Post #1

I found an old Post from Aladin and adapted.

Aladin or HotPepper, if you guys come along and can spare a moment and can explain how this works... I am tying this into the last formula explanation.
I just cannot seem to get my head around how to go about thinking about this correctly to get to understand it.

=LEN(TRIM(RIGHT(" "&Y6,LEN(" "&Y6)-SEARCH("#",SUBSTITUTE(" "&Y6," ","#",LEN(" "&Y6)-LEN(SUBSTITUTE(" "&Y6," ","")))))))

=TRIM(RIGHT(" "&Y6,LEN(" "&Y6)-SEARCH("#",SUBSTITUTE(" "&Y6," ","#",LEN(" "&Y6)-LEN(SUBSTITUTE(" "&Y6," ",""))))))

This question ^ is asking for a logical explaination of the formula..... Post #2


I was just asking for a reasoning behind all the extra calculation when 1 simple function returns the same thing, and I believe Doug is still looking for a logical explaination of the thought process that goes behind coming up with one of these hardcore formulas? Isn't that the base of this whole post.....?

 

[jindon]

That is only what you think.

I posted what I thought. so explained.

Anything wrong?

 

[DougStroud]

Actually Blazon,
I asked to find two things, 1) what are the last characters after the last space and the second thing 2) what is the length of these characters after the last space.
And this needs to be accomplished w/out any intermediary steps, Galileogali's and Jindon's methods work, Jindon's just needed a slight edit.

 

[blazonQC]

Anything wrong?

No, lol. I didn't mean for you to take that the wrong way if you did. I didn't realise that Doug wanted two seperate formulas, that's why I asked that question. I thought it was supposed to be the characters after the last space AS WELL as the length. It was my mistake and I meant no offense by it. I apologize.

 

[DougStroud]

Blazon,
No offense, none at all. I just wanted to be clear in answering the your reponses so that you could see what my objective is. And no reason to apologize, you were just offering a solution based on what you thought was the desired result. I am always grateful for any help offered

And yes, just as much as the answer's importance, I am still working on learning how to write and formulate a complex formula of this nature. I see what is doing by stepping through it, just cannot seem to get how to build it. Whether I am to build it left to right, or from right to left, or from the center out...

Cheers Mate,

Doug

 

[blazonQC]

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",40)),40))
=LEN(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",40)),40)))

Realised you could do the same thing w/o the use of LEFT at all. Would you like me to walk you through the thought process of this formula Doug? This version would be much easier to explain.

 

[DougStroud]

Hi Blazon,
Cannot work on it tonight.... have a long night of work ahead of me. Maybe tomorrow or next time you are around. I will also take a look at it and post back. Thanks for the help.

Doug

 

[István Hirsch]

A shorter formula to achieve that goal:

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))

 

[István Hirsch]

A shorter formula to achieve that goal:

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))

Sorry, I realize now that it is an old question with similar answers.