Counting Sheep I Mean Space

[buildmeup]

Hey everyone,

I am currently attempting to separate a string of text where I want the text after the last space in the string of text. I have attempted numerous ways via counting the number of spaces by attempting the text to column method, as shown in the attached spreadsheet but unsuccessful for some reason in the nested formula I have created. I also attempted to count the number of characters up to the last space to return whatever came after but was also unsuccessful.

Can anyone see where I am making a mistake and provide some guidance. If possible, I would like to steer away from VBA since I am still somewhat of a novice in regards to it.

Thanks ahead for your assistance!

[sandy666]

F2=isblank ==>>> ISBLANK(F2) ... etc...

[buildmeup]

I don't understand what you mean Sandy666

[sandy666]

in G2 you've this: =IF(F2=isblank,E2,IF(E2=isblank,D2,IF(D2=isblank,C2,IF(C2=isblank,"check",B2))))

but it should be: =IF(ISBLANK(F2),E2,IF(ISBLANK(E2),D2,IF(ISBLANK(D2),C2,IF(ISBLANK(C2),"check",B2)))) so copy that and paste into G2 then drag down as far as you need

I did not check if the formula works correctly only if all functions are correct

[daddylonglegs]

.......I want the text after the last space in the string of text.

Here's one way to do that

=REPLACE(A2,1,LOOKUP(2^15,FIND(" ",A2,ROW(INDIRECT("1:"&LEN(A2))))),"")

or simpler.....

=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),99))

[buildmeup]

Thanks for both responses! Daddylonglegs I was not aware of the TRIM and REPT functions via your second response. I like it!

[sandy666]

you can use PowerQuery without any formulas