LinkBack URL
About LinkBacks
Attachment 541811
Hello,
I'd like to extract the BNR # from column A and show it as I mocked up in B. I have a sheet with 1000s of records without a consistent format. Any help would be appreciated.
Thanks!
Megan
Attachment 541811
Hello,
I'd like to extract the BNR # from column A and show it as I mocked up in B. I have a sheet with 1000s of records without a consistent format. Any help would be appreciated.
Thanks!
Megan
Hi Megan, welcome to the forum!
I would use a helper column here (2 columns with formulas).
B1 =TRIM(RIGHT(SUBSTITUTE(A1,"BNR",REPT(" ",LEN(A1))),LEN(A1)))
C1 =IFERROR(LEFT(B1,FIND("/",B1)-1),B1)
Drag down as far as needed.
ARRAY formula in B2, then drag down
=IFERROR(LARGE(IF(ISNUMBER(LEFT(RIGHT(A1,LEN(A1)-FIND("/BNR ",A1)-5),ROW(INDIRECT("1:"&LEN(A1)-FIND("/BNR ",A1)-5)))+0),LEFT(RIGHT(A1,LEN(A1)-FIND("/BNR ",A1)-5),ROW(INDIRECT("1:"&LEN(A1)-FIND("/BNR ",A1)-5)))+0,""),1),"")
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
Thank you this worked!
Thank you for coming back to let us know. Which solution worked for you?
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Here is another one
Enter in B1 and copy down
Formula:
Please Login or Register to view this content.
v A B 1 This text is just a test/text /just a test /BNR 321654 321654 2 This text is just a test/text /BNR 987654323.25/Additional requirements 987654323.3 3 This text is just a test/text /BNR 156.35/CSST Testing 156.35 4 This text is just a test/text / additional text/BNR 89456123.2455/Status/Open 89456123.25
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks