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

  • Thread starter Thread starter Aris
  • Start date Start date
A

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.
 
I found out recently you can use LEN inside your
function. What exactly are you trying to do?
 
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
 
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
 
You could try =MID(D14,FIND(" ",D14)+1,1000) but it relies on tehre only
being one space as it counts from the left!
 
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?
Click to expand...

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.
 
Back
Top