Hello,
I need a help to create a formula to extract one text starting with same first four characters. Please see attached file.
For example column A is copy of CSV file and need to extract reference number starting with "ABCD"with two alphabets and digits assigned with it. The reference number does not align when use "text to columns"
Thanks!
Hi,
first attempt: in B7
=TRIM(MID(A7,SEARCH("ABCD",A7),15))
to be copied down.
Regards
-----------------------------------------------------
At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.
Please, mark your thread [SOLVED] if you received your answer.
Thanks! related to same, is it possible to extract further digit numbers only from reference number which can vary from 1 characters to 10 characters. see expected answers in attached file in column C.
Thanks for your help!
Hi
a very inefficient approach to get the max number (1 to 1000000) could be
=AGGREGATE(14,6,ROW(1:1000000)/ISNUMBER(FIND(ROW(1:1000000),MID(A7,SEARCH("ABC",A7),15))),1)
maybe a more modest contribution to global warming from
=TRIM(MID(MID(A7,SEARCH("ABC",A7),15),AGGREGATE(15,6,FIND(ROW($1:$10)-1,MID(A7,SEARCH("ABC",A7),15)),1),10))+0
Last edited by canapone; 03-09-2020 at 10:21 AM.
Hi,
Thanks for reply. Would it be easy to do a formula to get numbers from values in column B13 so that in Column C13 only numbers and this way don't have to go to A7. (I need both columns).
Example : column B13 ABCDPP119948 and Column C13 should be 119948
Thanks!
Hi again
in B7 to be copied down along the strings
=MID(A7,SEARCH("ABCD",A7),15)
in C7
=--MID(B7,AGGREGATE(15,6,FIND(ROW($1:$10)-1,B7),1),15)
or
=MID(B7,AGGREGATE(15,6,FIND(ROW($1:$10)-1,B7),1),15)+0
Regards
Last edited by canapone; 03-09-2020 at 10:28 AM.
Thank you very much!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks