# Extracting Inconsistent Data

1. ## Extracting Inconsistent Data

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

2. ## Re: Extracting Inconsistent Data

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.

3. ## Re: Extracting Inconsistent Data

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),"")

4. ## Re: Extracting Inconsistent Data

Thank you this worked!

5. ## Re: Extracting Inconsistent Data

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.

6. ## Re: Extracting Inconsistent Data

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

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1