EXTRACT Data, Left Side of Character "/" - various conditions

[ionelz]

Hi,
I want to Extract Data from Left side of character "/"
Data looks like :

12345678/001
BKJ12345/009
JG123456/099 - name 2
77661199/008 - name 1


01. IF all are Numbers (...all in left side of "/")
02. IF first two are letters and the rest numbers (...in left side of "/")
03. IF first 3 are letters and the rest numbers
04. IF Left side =K2 (data in K2)

Thank you all

[omahaNative_1023]

to get data from left side of string where "/" is the breakpoint, do this

Please Login or Register  to view this content.

[FlameRetired]

With data in column A this in B1 and filled down.

Please Login or Register  to view this content.

	A	B
1	12345678/001	12345678
2	BKJ12345/009	BKJ12345
3	JG123456/099 - name 2	JG123456
4	77661199/008 - name 1	77661199

[ionelz]

I need 4 formulas,
First to extract ONLY if in left side of "/" are NUMBERS (all of them)
Second if in left side of "/", first 2 are letters the rest numbers...
....

[omahaNative_1023]

sorry flame. i forgot to include find as part of worksheet formula. mine was code

[FlameRetired]

If I've interpreted correctly this in B1 and down.

Please Login or Register  to view this content.

	A	B
1	12345678/001	12345678
2	BKJ12345/009	BK
3	JG123456/099 - name 2	JG
4	77661199/008 - name 1	77661199
5	B4KJ12345/009	FALSE

[ionelz]

Thank you so much, I believe you got it...
But I need SEPERATE formulas :

First Formula, EXTRACT if all in left side of "/" are numbers
Result should be
12345678/001
77661199/008 - name 1


Second Formula, EXTRACT if first 2 are letters and the rest numbers , in left side of "/"
Result should be
JG123456/099 - name 2

Third Formula, EXTRACT if first 3 are letters and the rest numbers , in left side of "/"
Result should be
BKJ12345/009

[ionelz]

Maybe this question make more sense ,IF length of STRING is 8, how to check IF :
- first 2 are LETTERS and rest of 6 are NUMBERS

[JeteMc]

It may help us understand if you would provide an .xlsx file that manually shows the results that you would like to automate using formulas/code.
Instructions for uploading an .xlsx file are given in the banner at the top of the page.
Let us know if you have any questions.

[ionelz]

It may help us understand if you would provide an .xlsx file that manually shows the results that you would like to automate using formulas/code.
Instructions for uploading an .xlsx file are given in the banner at the top of the page.
Let us know if you have any questions.

Please see attached
I believe I did a wrong title to THREAD after i realized that I do not need to look in Left side of character "/"
I need to EXTRACT data from B to H, J and L
So conditions for EXTRACT are in first 2 and 3 characters

First Formula : extract data from B as is, IF first 2 characters are both NUMBERS
Second Formula : extract data from B as is, IF first 2 characters are both LETTERS followed by a NUMBER
Third Formula : extract data from B as is, IF first 3 characters are all LETTERS

[Bo_Ry]

Please try at
H2
=IFERROR(INDEX($F$1:$F$8,AGGREGATE(15,6,ROW($F$2:$F$8)/ISNUMBER(-LEFT($F$2:$F$8)),ROWS(H$2:H2))),"")

J2
=IFERROR(INDEX($F$1:$F$8,AGGREGATE(15,6,ROW($F$2:$F$8)/ISNUMBER(-MID($F$2:$F$8,3,1))/ISERR(-MID($F$2:$F$8,2,1)),ROWS(J$2:J2))),"")

L2
=IFERROR(INDEX($F$1:$F$8,AGGREGATE(15,6,ROW($F$2:$F$8)/ISNUMBER(-MID($F$2:$F$8,4,1))/ISERR(-MID($F$2:$F$8,3,1)),ROWS(L$2:L2))),"")

[ionelz]

Please try at
H2
=IFERROR(INDEX($F$1:$F$8,AGGREGATE(15,6,ROW($F$2:$F$8)/ISNUMBER(-LEFT($F$2:$F$8)),ROWS(H$2:H2))),"")

J2
=IFERROR(INDEX($F$1:$F$8,AGGREGATE(15,6,ROW($F$2:$F$8)/ISNUMBER(-MID($F$2:$F$8,3,1))/ISERR(-MID($F$2:$F$8,2,1)),ROWS(J$2:J2))),"")

L2
=IFERROR(INDEX($F$1:$F$8,AGGREGATE(15,6,ROW($F$2:$F$8)/ISNUMBER(-MID($F$2:$F$8,4,1))/ISERR(-MID($F$2:$F$8,3,1)),ROWS(L$2:L2))),"")

Thank you very much, again !