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.
|
Lista Forumurilor Pe Tematici
|
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login
POZE MRRRR'S FORUM (VIEW ONLY)
Nu sunteti logat.
|
Nou pe simpatie:
BrianaS la Simpatie.ro
 | Femeie
23 ani
Bucuresti
cauta Barbat
26 - 57 ani |
|
Mrrrr
AdMiN
 Inregistrat: acum 18 ani
Postari: 2247
|
|
Source:
Removes last two characters from contents of A2
=LEFT(A2;LEN(A2)-2)
Removes first 8 characters from A3 (B1)
=RIGHT(A3;LEN(A3)-8)
_______________________________________
|
|
| pus acum 11 ani |
|
TonyTzu
Moderator
 Inregistrat: acum 12 ani
Postari: 252
|
|
Removes some characters from start and some from end
=MID(F47;FIND("IL.";F47)-0;FIND(" -";F47)-FIND("IL.";F47)+0)
|
|
| pus acum 7 ani |
|
TonyTzu
Moderator
Inregistrat: acum 12 ani
Postari: 252
|
|
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:
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:
|
|
| pus acum 7 ani |
|
TonyTzu
Moderator
Inregistrat: acum 12 ani
Postari: 252
|
|
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 :
|
|
| pus acum 7 ani |
|
TonyTzu
Moderator
Inregistrat: acum 12 ani
Postari: 252
|
|
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
|
|
| pus acum 7 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 795
|
|
Extract a certain character in the middle of a string:
=MID(text;start_num;num_chars)
=MID(A2;12;1)
|
|
| pus acum 6 ani |
|