Hi all, I have some text string's where i want to keep only the numbers and special characters. Not all the strings have letters but some do.
An example is below
5/1
26/5
69/10
21/10F
I would want these to show as
5/1
26/5
69/10
21/10 (so the F is removed).
The letter (if there is one) will always be the last charterer if that helps?
Try:
Formula:
Please Login or Register to view this content.
let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source
If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE
Walking the tightrope between genius and eejit...
Hi,
Providing a sample workbook with maximum cases to understand the data discipline .
Punnam
Formula:Please Login or Register to view this content.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Or this
=IF(ISERROR(RIGHT(A1)+0),LEFT(A1,LEN(A1)-1),A1)
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Hi all, they work well when there is only one letter, but in some instances there are 2 letters at the end. Sorry for not clarifying this.
With example attachment
This should do it
=IFERROR(LEFT(A1,MATCH(1,(CODE(MID(A1,ROW($A$1:$A$255),1))<90)*(CODE(MID(A1,ROW($A$1:$A$255),1))>=65),0)-1),A1)
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
11/4F 11/4 10/1 10/1 66/1 66/1 59/20 59/20 6/1 6/1 17/10JF 17/10 9/2 9/2 8/5F 8/5
Thanks for your reply Alkey, was hoping to avoid array formula's if possible due to the additional memory and time taken to calculate. I have quite a few lines to work through so if possible was hoping the code would be standard formula type
=LEFT(A1,MAX(INDEX(((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<65)+(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>90))*ROW(INDIRECT("1:"&LEN(A1))),0)))
Try this
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Thats great. Thanks alot
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks