• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find last instance of a character in string and keep anything that comes before

C

ccbrandy

New Member
Hi guys,

I have the following data and I need to get rid of the numbers. This means I will have to find that last occurence of the character " " and keep everything that comes before however I dont know how to write the formula.

The length of the text varies and it can also be multiple words. The numbers are not longer than 3 digits.

String

abc 61

abc 79

ac 1

ab 123

a bc 12


Thanks!
 
Hi ccbrandy,

Welcome to Chandoo.org forum.

If the numbers are occuring only ones and in the last always, you can try below formula.

=MID(A1,1,FIND(" ",A1,MIN(IFERROR(SEARCH({1;2;3;4;5;6;7;8;9;0},A1),"e"))-1)-1)

Note: this is an array formula, so must be entered with Ctrl+Shift+Enter.

Regards,
 
@ThrottleWorks

Actually the logic is to find first number so search function will return the position number if find say 6,7 and for rest it will return #VALUE error so iferror will replace the error with "e", now any aggregate function ignores text so we will get rid of errors and min will return first position with number. You cannot use 0 as min would have return than 0, you can use any text "" , " ".

Regards,
 
The below formula will work only if

1) there are maximum 3 digits in the value
2) digits are at the end of the value

=TRIM(IF(ISNUMBER(RIGHT(A1,3)*1)=TRUE,LEFT(A1,(LEN(A1))-3),IF(ISNUMBER(RIGHT(A1,2)*1)=TRUE,LEFT(A1,(LEN(A1))-2),IF(ISNUMBER(RIGHT(A1,1)*1)=TRUE,LEFT(A1,(LEN(A1))-1)))))

P.S. - This is alternate way, should be avoided in real scenario.
 

Attachments

  • Book4.xlsx
    8.5 KB · Views: 3
Last edited:
Here are some more which should work (normally entered):
=TRIM(LEFT(SUBSTITUTE(A2," ",REPT(" ",100),LEN(A2)-LEN(SUBSTITUTE(A2," ",""))),100))

=LEFT(A2,LOOKUP(1,1/(MID(A2,ROW($A$1:$A$99),1)=" "),ROW($A$1:$A$99))-1)

Somendra's formula can be tweaked little to avoid CSE entry:
=LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1)
 
Not sure. Am I missing something obvious?

I am attaching workbook as I had tried those solutions.
 

Attachments

  • LastCharacterFormulae.xlsx
    9.4 KB · Views: 1
Ah. Good spot, Somendra.

That can be blamed on binary search that LOOKUP does. This tweak should fix it.
=LEFT(A6,LOOKUP(1,1/((MID(A6,ROW($A$1:$A$99),1)=" ")*ROW($A$1:$A$99)),ROW($A$1:$A$99))-1)
 
Hi Shriva, nice formulas. I think the lookup value should be greater than any value in the lookup range for this type of approach to be robust so changing the lookup value from 1 to 2 could also fix that issue. Another possibility is:

=LEFT(A2,MATCH(2,INDEX(1/(MID(A2,ROW($A$1:$A$99),1)=" "),))-1)

which uses the INDEX function to avoid array entry (a trick picked up from Barry Houdini on SO.)
 
Thank you both Lori and Haseeb. :oops: Looks like I missed easier one.

I do not have anything against ARRAY entry but it took me some time to get used to it so I try to search for a non array entry alternative where possible.
 
Haseeb, yeah only a few seconds apart :)

In Excel 2013 this is a good example for Flash Fill. After entering the first three values you want to return (abc,abc,ac) just press Ctrl+E to fill the others.
 
You must log in or register to reply here.
Back
Top