-2

I have text like:

#1 Supermarket 3454
Ubermarket 23478
Single_Token
Two Tokens
As many tokens as you like

And I'd like to remove the last number (when it appears) so I'm left with:

#1 Supermarket
Ubermarket
Single_Token
Two Tokens
As many tokens as you like

The data is added to regularly, so I would appreciate using an excel formula and not a mandrolic method using column splitting, or the like.

However I can't figure out how to use find to find all numbers, or substitute to only take number off the end.

edit: as people seem to neeed me to prove my accepted answer works, here is a screenshot:

enter image description here

  • Does you want to do this by formula, or VBA user-defined function is safe for you? – Akina Jul 24 at 13:23
  • @Akina I'd rather not dabble in VBA because it means duplicating the code in all the spreadsheets I need this in. – Pureferret Jul 24 at 13:28
  • it means duplicating the code in all the spreadsheets Why? you can place it into common module - it will be available on all sheets in this workbook. You can place it into personal macro storage - it will be available in all workbooks on your workstation. – Akina Jul 24 at 13:32
  • @Akina I did not know about personal macro storage – Pureferret Jul 24 at 13:42
  • Try to start a macro recording, you will be asked about where does you want to place this macro to. – Akina Jul 24 at 13:51
1

Would there only be numbers at the very end you need to take off? Nothing like #1 Supermarket 33 and 12345 or #1 Supermarket 12442 12321?

In that case you can take of the last word of the string using:

=LEFT(TRIM(A1),FIND("~",SUBSTITUTE(A1," ","~",LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))))-1)

enter image description here

Edit:

After your comments below I believe you can wrap it in an IF statement to check if last char is a number and if there are spaces in the string, like so:

=IF(AND(ISNUMBER(RIGHT(A1,1)*1),LEN(A1)-LEN(SUBSTITUTE(A1," ","")>0)),LEFT(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1),A1)
  • No, nothing except a number at the end. How does this work? – Pureferret Jul 24 at 13:05
  • Relatively simple; through SUBSTITUTE we first get the number of spaces in the string (full length minus lengt when spaces are substituted out). Now we know the amount of spaces we can use that number in a second substitute as our instance number to only change the last space into a non occurent character. At last we use that character in a FIND to get the correct number of chars from the left. You can go through the formula yourself using formula evaluation. – JvdV Jul 24 at 13:14
  • Applying it to more data I have, it looks like it removes the last token, not the last number. – Pureferret Jul 24 at 13:18
  • Hence why I asked you how your data looks. If there is a number at the end of all the string you have, then this will work as you can see at the example data I used. – JvdV Jul 24 at 13:24
  • Apologies, I took what you said as meaning "do you only want to take off number at the end, nothing else" which is not what you said. – Pureferret Jul 24 at 13:27
-1

I would like to suggest the most easiest & built in method to separate Text left to the numbers is, Text to Column command.

enter image description here

  • From Data Tab hit Text to Column then pick Fixed Width option then Next.

  • You get this:

enter image description here

  • Hit the Finish button.

You get this:

enter image description here

  • This won't work as I'm constantly adding new rows of data and performing the same operation over and over will take up more time than a formula that will automatically run. – Pureferret Jul 24 at 13:43
  • @Pureferret,, my simple suggestion is, edit the post & mention that data will be update frequently therefore you need any swift method,, also try to update sample data with as many possible values considering the change,,, since I've created this answer on the basic of information available with the post (Question) !! – Rajesh S Jul 24 at 14:15
  • This was a legitimate solution to the question as originally posed. With the question's update and information in the comment that is missing in the question, the OP has clarified that the data is more complex than originally implied, and this won't handle it. Under the circumstances, the downvote is a bit harsh. Nevertheless, the answer doesn't address the question as now clarified, so it's kind of fair game. You might want to save your rep and just delete this since it won't help anyone. It was a good quality answer, though. – fixer1234 Jul 25 at 4:48
  • @fixer1234,, it's not the question to save rep,, answer is all around the original post,, and Edit comes into air,, just few Hrs before, with older data set, was down voted long back !! – Rajesh S Jul 25 at 6:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.