Formula to find characters after the last space in a string

DougStroud

Well-known Member
Joined
Aug 16, 2005
Messages
2,963
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
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

DougStroud

Well-known Member
Joined
Aug 16, 2005
Messages
2,963
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," ",""))))))
 

blazonQC

Board Regular
Joined
Jun 6, 2007
Messages
92
Hi,

IMHO, there is an easier way, but are you really wanting just formulas?

Code:
Public Function lastspaceright(str As String) As String
Dim i As Long
Dim j As Long

i = 1
j = 1

Do
j = InStr(j, str, " ", vbBinaryCompare)

     If j <= 0 Then
        Exit Do
       Else
        i = j
        j = j + 1
     End If

Loop

lastspaceright = Right(str, Len(str) - i)

End Function
Note: Insert a module and place this function in it.

Ex.

Cell A1 value = "This is what it does"
Cell B1 value = "=lastspaceright(a1)"
Cell C1 Value = "len(b1)"

returns

A1 = "This is what it does"
B1 = "does"
C1 = "4"

Will this work? Or do you still want an explanation of the above minboggling formula?

Chad
 

DougStroud

Well-known Member
Joined
Aug 16, 2005
Messages
2,963
Hi Blazon,
Thanks, I will keep this for future use. But yes, I want to learn the formula constructs.... once mastered it will help in Excel and VBA.

thanks
 

galileogali

Well-known Member
Joined
Oct 14, 2005
Messages
748

ADVERTISEMENT

Another version....
{=REPLACE(A1,1,MAX((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=" ")*ROW(INDIRECT("1:"&LEN(A1)))),"")}

To Confirm, CTRL+SHIFT+ENTER

blazonQC you can to see VBA function InStrRev


GALILEOGALI
 

DougStroud

Well-known Member
Joined
Aug 16, 2005
Messages
2,963

ADVERTISEMENT

G-
That is impressive!
 

DougStroud

Well-known Member
Joined
Aug 16, 2005
Messages
2,963
I think one of my basic questions is, what question do you ask to properly formulate the logic for the formula, e.g., I want the last values after the last space.
How do you think about that to get the right formula functions and in the right order.

For instance, in this one:
=TRIM(RIGHT(" "&Y7,LEN(" "&Y7)-SEARCH("#",SUBSTITUTE(" "&Y7," ","#",LEN(" "&Y7)-LEN(SUBSTITUTE(" "&Y7," ",""))))))

Would I build this in pieces or do I need to know how to build it right off?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,385
Messages
5,601,331
Members
414,446
Latest member
CRAVIN

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top