LinkBack URL
About LinkBacks
I have a column of data where there are spaces before the text and after with characters such as /!*1I.
Looking a formula to clean the data columns eg France!/ to read France and son .
As per sample file , I have shown some examples in columns B.
I have a column of data where there are spaces before the text and after with characters such as /!*1I.
Looking a formula to clean the data columns eg France!/ to read France and son .
As per sample file , I have shown some examples in columns B.
See the attached file.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
The best way to do this is with a series of nested SUBSTITUTE formulae, wrapped up inside a TRIM, like this:
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Tabel1[@ORIGIN],"/","")," I",""),"*",""),"!",""),"1",""))
Note I used " I" and not "I"as that would have removed the I in Ireland. I would not have allowed that....
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Try any of these two
Formula:
Please Login or Register to view this content.
Or
Formula:Please Login or Register to view this content.
v A B 1 ORIGIN Expected results 2 Hongrie / Hongrie 3 France France 4 Italie Italie 5 Allemagne / Allemagne 6 Belgique! Belgiqu 7 Belgique / Belgique 8 Mande Mande 9 Irlande Irlande 10 Danemark / Danemark 11 Saint-Marin / Saint-Marin 12 espagne / espagne 13 Rep. Tcheque I Rep. Tcheque 14 Saint-Mann / Saint-Mann 15 Pologne / Pologne 16 Allernagne / Allernagne 17 Espagne! Espagn 18 Israel Israel 19 Hongrie I Hongrie 20 Espagne I Espagne 21 Portugal / Portugal 22 Rep. de Cone / Rep. de Cone 23 Rep. de Colt* / Rep. de Colt 24 Rep. de Coree / Rep. de Coree 25 Portugal 1 Portugal 26 Serbie Serbie 27 Pays-Bas / Pays-Bas
Last edited by AlKey; 09-16-2017 at 01:14 PM.
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
OR B2=LEFT(A2,LOOKUP(2,1/SEARCH(MID(SUBSTITUTE(SUBSTITUTE(A2&" "," I ","^^^"),"*","^"),ROW(INDIRECT("1:"&LEN(A2))),1),"abcdefghijklmnopqrstuvwxyz"),ROW(INDIRECT("1:50"))))
Try this and copy towards down
Please Login or Register to view this content.
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
oeldere, Glenn, Alkey thank you all for the solutions provided
All worked of them worked as charm.
nflsales
Thank you for your formula which worked as charm
Thanks for the rep, Jean. Alkey has the best formula, I think, - so go with it...
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Tabel1[@ORIGIN],"/"," "),"*"," "),"1"," "))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks