Formula to find characters after the last space in a string

DougStroud

Well-known Member
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

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

DougStroud

Well-known Member
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
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
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

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
Thanks Galileeogali

DougStroud

Well-known Member

ADVERTISEMENT

G-
That is impressive!

DougStroud

Well-known Member
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?

galileogali

Well-known Member
Can you transalate Spanish?

LOL, no sorry

Similar threads

Replies
1
Views
485
Replies
2
Views
240
Replies
10
Views
775
Replies
4
Views
510
Replies
6
Views
342

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Threads
1,163,855
Messages
5,834,001
Members
430,250
Latest member
Reggie Mcqueen

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

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