Formula to find characters after the last space in a string

DougStroud

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

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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," ",""))))))
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

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