LinkBack URL
About LinkBacks
Hello all,
In this example, how to extract the reference on the left only:
1069ANDRAA0013 expected: 1069
A230ANDRAA0013 expected: A230
14721ARSECA0181 expected: 14721
W101HAKEZH5003 expected: W101
Thank you very much in advance.
Hello all,
In this example, how to extract the reference on the left only:
1069ANDRAA0013 expected: 1069
A230ANDRAA0013 expected: A230
14721ARSECA0181 expected: 14721
W101HAKEZH5003 expected: W101
Thank you very much in advance.
Using Power Query
Excel 2016 (Windows) 64 bit
1069ANDRAA0013 1069 A230ANDRAA0013 A230 14721ARSECA0181 14721 W101HAKEZH5003 W101
Sheet: Sheet1
Please Login or Register to view this content.
Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").
It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.
- Follow this link to learn how to install Power Query in Excel 2010 / 2013.
- Follow this link for an introduction to Power Query functionality.
- Follow this link for a video which demonstrates how to use Power Query code provided.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
B1=LEFT(A1,SEARCH({"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"},A1,2)-1)
This is an array formula.
Close with CTRL+SHIFT+Enter.
The brackets appear automaticly.
Last edited by oeldere; 05-26-2022 at 02:43 PM. Reason: add A -Z
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.
Thank you to both of you.
Alan, I don't want to use Powerquery at the moment since it is not allowed for every user in my company.
Oeldere, I tried your formula. It works for some rows and not for others. For example:
with S115CARDGC0021, I get S115C but I should get S115
with 14707CHEVSC0081, I get #VALUE! but I should get 14707
with 14707DELIAD0011, I get 14707DELI but I should get 14707
Is there any adjustment that we could make to the formula?
Thank you again.
How aboutFormula:
Please Login or Register to view this content.
Another approach.
B1=IF(ISNUMBER(MID(A1,5,1)*1)=FALSE,LEFT(A1,4),LEFT(A1,5))
Hi again oeldere and hello Fluff13!
Both solutions give exactly the same results, with only one error left! We're almost there!! I really appreciate that you share some of your time with me.
So among the 14K rows I have to work with, the last references that don't extract correctly are the ones like:
NOVU320DUFOSD0101
NOVU300FILTAF0011
I should get NOVU320 or NOVU300 but both formulas give me NOVU3
I could correct them manually but if one day new references appear with the same structure, I will not be able to extract them correctly. Is there any way to fix it?
Thank you
If you don't mind to use helper cells.
B1
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"0"," "),"1"," "),"2"," "),"3"," "),"4"," "),"5"," "),"6"," "),"7"," "),"8"," "),"9"," "))
C1
=LEFT(A1,FIND(MID(B1,IFERROR(FIND(" ",B1)+1,1),1000),A1)-1)
Regards.
My English is very poor, so please be patient >_<"
My Dynamic Related Dropdown post:
https://www.excelforum.com/excel-for...ml#post5657376
https://www.excelforum.com/excel-for...ml#post5655711
https://www.excelforum.com/excel-for...ml#post5655762
Another solution
=LEFT(A1,AGGREGATE(15,6,ROW($A$1:$A$100)/(1/((CODE(MID(A1,ROW($A$1:$A$99),1))<=57)*(CODE(MID(A1,ROW($A$2:$A$100),1))>57))),1))
it support case of
14A707CHEVSC0081
A14A404AHESS123
Regards.
Try
=LEFT(A2)&-LOOKUP(0,-MID(A2,2,{1,2,3,4,5}))
Try this:
=LEFT(A2,AGGREGATE(15,6,ROW($1:$10)/ISNUMBER(--MID(A2,ROW($1:$10),1))/ISERR(--MID(A2,ROW($2:$11),1)),1))
A0069ANDRAA0013 result is: A0069
NOVU320DUFOSD0101 result is: NOVU320
Good morning all,
I am really happy and relieved because Menem's and Phuocam's solutions work perfectly! You both make my day/week/month!
Bo_ry's gives an error, but thank you anyway for your time.
Thank you so much to all the helpers. Have a great day all.
S.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks