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:
micky_miha 23 ani
 | Femeie
23 ani
Bucuresti
cauta Barbat
23 - 43 ani |
|
Mrrrr
AdMiN
 Inregistrat: acum 17 ani
Postari: 2186
|
|
Assuming in cell A2 you have the following text:
:vendor:014154 MEGA SOFT HYGIENIC PRODUCTS INC :tel:365-2728 :buyer:301 CONNIE AVILA :fax:365-2727 :dept:000 *ALL :subDpt:000 *ALL :terms:030 30 DAYS :currency:PHILIPPINE PESO :discounts: %:status:3:type:C:dlvLocation:00881 MERKADO SUPERMARKET :dlvAddress:B106 G/F UPTOWN Center Katipunan Ave. Brgy. UP Campus QUEZON CITY QC
and you want to extract just this string:
dlvLocation:00881
This formula will work:
=LEFT(RIGHT(A2;LEN(A2)-SEARCH("dlvLocation:";A2)+1);SEARCH(" ";RIGHT(A2;LEN(A2)-SEARCH("dlvLocation:";A2)+1);1)-1)
Source:
_______________________________________
|
|
| pus acum 4 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 739
|
|
=LEFT(RIGHT(A2;LEN(A2)-SEARCH("dlvLocation:";A2)+1);SEARCH(" ";RIGHT(A2;LEN(A2)-SEARCH("dlvLocation:";A2)+1);1)-1)
Note that there are 3 SEARCH functions in the formula above.
In other formulas the syntax will be the same:
- the 1st SEARCH string is the same as the 3rd SEARCH string and is the string you want extracted or the beginning of that string
- the 2nd SEARCH string is at the end of the string you want to extract
And you "play" with the yellow marked strings in the formula above, in order to extract the string how you want it
|
|
| pus acum 3 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 739
|
|
A different formula for a string extraction, where first and last SEARCH functions have the string before the one I wanted to extract, which was between an open round bracket = ( and a minus sign = -
=LEFT(RIGHT(A1;LEN(A1)-SEARCH("(";A1)+0);SEARCH("-";RIGHT(A1;LEN(A1)-SEARCH("(";A1)-1);1)-1)
Row A
1 01AAA = Declaration of XXXX (ZZZ_ZZZ_Z - profile YYYYYYYY)
This long string I inserted via a software called Smart Type Assistant, by typing the following string /01AAA. The only thing that connects the 3 strings is 01AAA.
I had to extract the following strings:
string1 = Declaration of XXXX
string2 = ZZZ_ZZZ_Z
string3 = YYYYYYYY
So I needed these 3 formulas:
B1 =LEFT(RIGHT(A1;LEN(A1)-SEARCH("=";A1)-1);SEARCH("(";RIGHT(A1;LEN(A1)-SEARCH("=";A1)-1);1)-2)
C1 =LEFT(RIGHT(A1;LEN(A1)-SEARCH("(";A1));SEARCH("-";RIGHT(A1;LEN(A1)-SEARCH("(";A1)-1);1)-1)
D1 =LEFT(RIGHT(A1;LEN(A1)-SEARCH("-";A1)-8);SEARCH(")";RIGHT(A1;LEN(A1)-SEARCH("-";A1)-9);1)+0)
These formulas extracted:
string1 in column B
string2 in column C
string3 in column D
|
|
| pus acum 3 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 739
|
|
Instead of using formulas after the big string is inserted by using a 3rd party program, I made a VBA code that inserts the 3 strings for me.
Now all I have to do is select the existing strings in the form of NNLLL (N = number, L = letter), then click the button that links to the following code, and POOF! in the following 3 cells of each row the 3 strings appear.
For example, if I selected in A1:A3 the following strings:
01CCC
02CCC
14ZZZ
Then used the code below to insert in columns B1:D3 the 3 strings corresponding to each cell in A1:A3.
Sub STRING_INSERT()
Dim rng As Range
Set rng = Selection
For Each cell In rng
Select Case cell.Value
Case "01CCC"
cell.Offset(0, 3).Value = "Declaration of CCCCC"
cell.Offset(0, 1).Value = "CCCCC_CCC_C"
cell.Offset(0, 2).Value = "CCCCCC"
Case "02CCC"
cell.Offset(0, 3).Value = "Declaration of CCCCC"
cell.Offset(0, 1).Value = "EEEEE_EEEEEEEE"
cell.Offset(0, 2).Value = "DDDDDDD"
Case "14ZZZ"
cell.Offset(0, 3).Value = "Declaration of ZZZZZZ"
cell.Offset(0, 1).Value = "YYYY_WWWWWWW"
cell.Offset(0, 2).Value = "XXXXXXXX"
End Select
Next cell
End Sub
|
|
|
| pus acum 3 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 739
|
|
Extract entire string before a character.
I have some CAS Numbers from dangerous substances and need to extract just the first part of each.
For example, I have:
40088-47-9
58-89-9
115-29-7
8001-35-2 |
and need to extract only:
Formula is extracting all characters before the "-" character:
| =LEFT(F1;(FIND("-";F1;1)-1)) |
|
|
| pus acum 1 an |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 739
|
|
Extract text from the right of a string after a certain character.
Eg of string XXXXXX-YYYYYY/Z/AAA/B/CCCCCCC/DDDDDD
I need the string after XXXXXX-YYYYYY, thus I need: /Z/AAA/B/CCCCCCC/DDDDDD
=RIGHT(A2;LEN(A2)-FIND("/";A2)+1)
|
|
| pus acum 11 luni |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 739
|
|
Extract text between same characters:
From: 2023, 8, 13
I need to extract just the month, so: 8
But the month goes from 1 to 12, so it's dynamic.
This formula works:
=MID(C2;FIND(", ";C2)+1;FIND(", ";C2;FIND(", ";C2)+1) - FIND(", ";C2)-1)+1
****
Same example as above, but extract the day: 13, which ranges from 1 to 31, so it's dynamic
This formula works:
=MID(C2;FIND(", ";C2; FIND(", ";C2)+1)+2;256)
****
If the year would also be dynamic, the following formula would work to extract characters before the first ", ":
=LEFT(C2;FIND(", ";C2;1)-1)
|
|
| pus acum 4 luni |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 739
|
|
For the example from the post above, I actually needed to extract from the following string the date, price of gasoline and price of diesel:
data2.addRow([new Date(2023, 0, 1),6.4, 7.57]);
So i did the following formulas in cells B2 to G2 then dragged down:
B2 =RIGHT(A2;LEN(A2)-FIND("Date(";A2)-4)
C2 =IFS(RIGHT(LEFT(B2;12);2)="),";LEFT(B2;10);RIGHT(LEFT(B2;12);1)=")";LEFT(B2;11);RIGHT(LEFT(B2;12);1)<>")";LEFT(B2;12))
D2 =RIGHT(B2;LEN(B2)-LEN(C2)-2)
E2 =LEFT(C2;FIND(", ";C2;1)-1)
F2 =MID(C2;FIND(", ";C2)+1;FIND(", ";C2;FIND(", ";C2)+1) - FIND(", ";C2)-1)+1
G2 =MID(C2;FIND(", ";C2; FIND(", ";C2)+1)+2;256)
Now in H1, I1 and J1 I entered the following formula to extract Date, gasoline price and diesel price respectively:
H2 =DATE(E2;F2;G2)
I2 =SUBSTITUTE(LEFT(D2;FIND(", ";D2;1)-1);".";",")
J2 =SUBSTITUTE(LEFT(RIGHT(D2;LEN(D2)-FIND(", ";D2)-1);FIND("])";RIGHT(D2;LEN(D2)-FIND(", ";D2)-1);1)-1);".";",")
|
|
| pus acum 4 luni |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 739
|
|
I had to extract a variable number from the middle of a string between two / characters, number which can be represented as follows:
- whole number: 1000, 200, 50 or 3 - so four different lengths
- number with decimals (but the "wrong" decimal separator for my regional settings): 1.5, 5.0 etc.
The formula below does the job and in the process substitutes also the . with , as decimal separator.
=IF(B6="AAA"; SUBSTITUTE(LEFT(RIGHT(C6;LEN(C6)-FIND("/";C6;1));LEN(RIGHT(C6;LEN(C6)-FIND("/";C6;1)))-LEN(RIGHT(RIGHT(C6;LEN(C6)-FIND("/";C6;1));LEN(RIGHT(C6;LEN(C6)-FIND("/";C6;1)))-FIND("/";RIGHT(C6;LEN(C6)-FIND("/";C6;1));1)+1)));".";",");1)
|
|
| pus acum 4 saptamani |
|