Right Function that goes to space instead of set # of chars

[Aris]

Is there a function in Excel that works like RIGHT but
instead of taking a set number of characters, it will
take all characters until it finds a space?

Thanks.

 

[Marie]

I found out recently you can use LEN inside your
function. What exactly are you trying to do?

 

[bob Umlas]

Check out the MID function

 

[Dan E]

Aris,

Their is no built in function for that job, but here's one that works...

=RIGHT(A1,LEN(A1)-FIND(CHAR(164),SUBSTITUTE(A1," ",
CHAR(164),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))),1))

I used a ¤ as a character I thought would be uncommon, if you
need to change that, change the 164...

Semi explanation

LEN(A29)-LEN(SUBSTITUTE(A29," ","")) denote as A
A counts the number of spaces

SUBSTITUTE(A29," ",CHAR(164),A) denote as B
B substitutes the last space for a ¤

FIND(CHAR(164),B,1) i'll call this C from now on
C finds the place of the ¤

LEN(A29)-C finds the required length

=RIGHT(A1,LEN(A1)-C) pulls the req'd letters from the end

Dan E

 

[Andy Brown]

If you mean everything after "the one space", try
=RIGHT(A1,LEN(A1)-FIND(" ",A1))

If you mean everything after the *last* space, check David's SepLastTerm
code at
http://www.mvps.org/dmcritchie/excel/join.htm#seplastterm

Rgds,
Andy

 

[Dan E]

Just to prove how easy it is with VBA here's a custom function

Public Function RightTWO(InString As String, Separator As String) As String
RightTWO = Right(InString, Len(InString) - InStrRev(InString, Separator))
End Function

USE
Press Alt + F11 (Opens the VBA window)
Find the Project "VBAProject(YourWorkBookName.xls)
Right Click and choose Insert -> Module
Paste the above into the module's code window
Close the VBA window

In your worksheet put
=RightTWO(A1, " ")

For more info on macros and how to get started visit
http://www.mvps.org/dmcritchie/excel/getstarted.htm
David McRitchie's Site

Dan E

 

[Big Chris]

You could try =MID(D14,FIND(" ",D14)+1,1000) but it relies on tehre only
being one space as it counts from the left!

 

[Harlan Grove]

Is there a function in Excel that works like RIGHT but
instead of taking a set number of characters, it will
take all characters until it finds a space?

A few ways, but all involve multiple function calls.

=MID(A1,FIND(CHAR(127),SUBSTITUTE(" "&A1," ",CHAR(127),
LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1)),1024)

or the array formula

=MID(A1,MAX(IF(MID(" "&A1,ROW(INDIRECT("1:1024")),1)=" ",
ROW(INDIRECT("1:1024")))),1024)

Both will return the entire string in A1 if it contains no spaces.