Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Nou pe simpatie:
sayler_moon_19 la Simpatie.ro
 | Femeie
25 ani
Bacau
cauta Barbat
26 - 46 ani |
|
|
Mrrrr's Forum (VIEW ONLY)ReguliInregistrareLoginPozeNu sunteti logat. Lista Forumurilor Pe Tematici
|
| #2 |
|
|
Removes some characters from start and some from end
=MID(F47;FIND("IL.";F47)-0;FIND(" -";F47)-FIND("IL.";F47)+0)
|
|
| |
|
| #3 |
|
|
Delete text before a character
=RIGHT(A1,LEN(A1)-FIND(",",A1))
Delete text after a character
=LEFT(A1,FIND(",",A1)-1)
Delete text after Nth character
=LEFT(A1;FIND(".";A1;FIND(".";A1;FIND(".";A1)+1)+1)-1)
FIND() function takes three parameters. Third, while optional, is actually an offset where to start search. So, first FIND() finds the first dot, moves right by one, and passes that number into second FIND(). Second FIND() finds the first dot after the first dot (to say so), which means it finds second dot and moves one character to the right.
This number is finally passed to the third FIND(), which then basically finds first dot after the second dot, which is, obviously, the third dot.
Finally, position of the third dot minus one character (because you do not need the third dot) is passed to LEFT() function, which returns what you need.
Souce:
https://stackoverflow.com/questions/220 ... d-in-excel
Delete text after last character of a kind
=TRIM(LEFT(SUBSTITUTE(A1, "-", REPT(" ",255), MAX(1, LEN(A1)-LEN(SUBSTITUTE(A1,"-","")))), 255))
(This formula will remove everything after the last -)
Source:
http://www.ozgrid.com/forum/showthread.php?t=161227
|
|
| |
|
| #4 |
|
|
You can remove ALL CHARACTERS from a cell before a symbol by using Find and Replace:
For example, if you want to remove all characters before :, you go to Find and Replace and:
Find: *:
Replace:
So you search for *: and replace with nothing. The wildcard replaces all characters before :
Same goes for :*, removes all characters after :, including :.
If you want to keep :, you will replace :* with :
|
|
| |
|
| #5 |
|
|
To quickly remove line breaks (those made with ALT+ENTER within a cell to have text on multiple lines), do this:
- open search and replace (CTRL+H)
- in the Find what field, hold ALT and type 010 then release ALT (a small . should appear in there)
- leave the Replace with field empty
- hit the Replace all button
|
|
| |
|
| #6 |
TRaPModerator
Postari: 960
|
|
Extract a certain character in the middle of a string:
=MID(text;start_num;num_chars)
=MID(A2;12;1)
|
|
| |
|
